Thursday, May 31, 2018

Use ALTER COLUMN statement with a default() mask to apply data masking

Here is what I am going to do.

Here is a query that retrieves FirstName, LastName, City from Employees table in the Northwind database on my SQL Server 2016 SP2 named instance OHANA.

Use Northwind
Go
SELECT FirstName, LastName, City
FROM Employees


Now I will apply a datamask to the FirstName column with a default() datamask using the following Alter Table, Alter Column statements.
-----------------------
Use Northwind
Go
Alter table Employees
Alter Column FirstName nvarchar(10) Masked with (Function='default()')
-----------------------------
When I run the above query, the query runs without errors. whenever I run the query, the data is not masked for me, because I have permissions.

To test the masking I create a user, hacker3 as follows:
--
/* create a hacker3 user*/
CREATE USER hacker3 WITHOUT LOGIN;
GRANT SELECT ON EMPLOYEES TO hacker3;
EXECUTE AS USER='hacker3'
----

DataMask_01
 Now I run a SELECT query, the same one we used earlier and get this response.


DataMask_02

You will observe that the FirstName column is masked. Note that the FirstNames in the table ranges from having 4 to 8 letters but the default() masking function replaces them with 3 xxx's.

Note that if you run a new query, you are not running as hacker3.

I have used the 'hacker' a couple of times while testing.



Tuesday, May 29, 2018

Get the latest version of Report Builder 3

I believe the latest build of Report Builder 3 is version 14.0.1016.246 published 2/16/2018 (23.0MB). I had this earlier version 14.0.344.82 published 3/9/2017 (22.6MB).

Go to this link and download the latest Report Builder 3


If you all ready have Report Builder 3 (earlier version) you will be asked to repair or remove.
Accept 'Repair option and click Next to Install.

This is the program created on your computer.


Report Builder 2 and Report Builder MSDN sites still active.

Registering SQL Servers in SSMS v17.7

When you have a large number of SQL Servers it becomes a no brainer to find a way to manage them effectively. SQL Server Management Studio can be very effective in managing local servers by grouping them according to your usage necessities.

Registering SQL Server instances makes it easy to administer. Connecting to servers is a lot faster if you register all the instances in the Local Group of Servers. In SQL Server Management Studio 17.7 it is lot easier to register than in previous, older versions.

I have three SQL Server instances from 3 different versions. After registering I just need to double click the server I am using and I am ready to roll.


Watch the Video:

Things new in SQL Server 2016

In SQL Server 2016,

Configure multiple TempDB database files during Installation and set up.

The Query Store (new) stored texts, execution plans and performance metrics with the database. You have access to its dashboard related to query performance.

[image]

Availability of Temporal Tables (history) which records all data changes.

Built-in JSON Support(new). You can import/export, save and parse in JSON.

Polybase(new) query engine integrated SQL Server with external data in Hadoop or Azure Blob storage. Import/export and executing queries all possible.

Stretch Database(new) lets you dynamically, securely archive data from local SQL Server Database to an Azure cloud SQL database. querying is automatic both local and remote data by linked databases.

In-memory OLTP:
Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.
Supports tables up to 2TB (up from 256GB).
Has column store index enhancements for sorting and Always On Availability Group support.

New security features:
Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.
Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.
https://hodentekmsss.blogspot.com/2018/05/data-masked-column-in-table.html


Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.

Monday, May 28, 2018

Data masked column in a table

If the table belongs to a database, then in the context of the database run the query:

SELECT * FROM sys.columns

If the column(s) is masked you should look for the column 'is_masked' in the response as shown.


Here is an example. The FirstName column in the Employees table in Northwind database is masked using the default masking function. When you run the above query this is what you will see.



Friday, May 18, 2018

Upgrading SQL Server Managment Studio v17.3

SQL Server Management Studio is no longer installed when you install SQL Server. It is a separate installation.

If you have SQL Server Management Studio Installed you may get a pop-up message informing you of an update. Even otherwise you can access the update using the following from your version of SSMS.


This post specifically related to upgrading from v17.3 to v17.7. If you are doing for the first time, you may install v17.7.

Here are some images of the upgrading.









This is also on a video:

Download link for the latest version:

Wednesday, May 16, 2018

Review SQL Server Versions and download SQL Server 16 version 13.0.5206.0 SP2

There have been many versions of SQL Server released over a long period of time and of course these are followed up by Service Packs (SP). It is hard to know which version is going to be installed.

There is an unofficial chart lists of all the known SP;s, Cumulative Updates(CU), patches and hot-fixes from SQL Server 7.0 to SQL Server 2017.

SQL Versions.png


I have a reasonably latest version of SQL Server 2016 Developer edition:
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)   Jul  6 2017 07:55:03   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 17134: ) (Hypervisor) 

The following version download is here:
13.0.5206.0 4/9/2018; SQL Server 2016 SP2
https://www.microsoft.com/en-us/download/details.aspx?id=56836

Download details for SQL Server 2016 SP1 with the following details:
https://www.microsoft.com/en-us/download/details.aspx?id=54276
---------------
Version:            13.0.4001.0
File Name:          ENU\SQLServer2016SP1-KB3182545-x64-ENU.exe
Date Published:     11/15/2016
File Size:          551.8 MB

----------------

Saturday, May 5, 2018

Reporting Services spawns an error if reporting services database is unavilable

Reporting Services databases are stored in the Reporting Services engine and for SQL Server 2016, you can find them in SQL Server Managment Studio 2016.

If you try to access Reporting Services URL, you will get the following error if the SQL Server hosting Reporting Services has not started.

Here is the error.



Here is the (start/stop) state of the related Database engine.


Here is the Reporting Services after starting the Database engine.


SQL Server 2017 Performance Benchmarks

TPC is an acronym for Transaction Processing Corporation , a non-profit organization to define transaction processing benchmarks and prov...