Monthly Archives: February 2016

Dynamic Data Masking – SQL Server 2016

–by Ginger Keys

Dynamic Data Masking is a SQL Server 2016 feature that can be deployed as part of your overall security strategy. Data masking is not intended as a primary security solution, but provides a way to obfuscate or hide characters in data such as social security numbers, credit card numbers, email addresses, and other possibly sensitive data so that end users (or non-privileged users) are not able to see this sensitive data.   Data masking hides data by placing X’s or 0’s in place of characters in a data field. For example a masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. A social security number could look like xxx-xx-2345, or an email could display as Gxxxxxx@xxxxx.com.

Let’s be clear…Data masking is not encryption! With data masking, data on the back end is still visible. True encryption like transparent data encryption (TDE) and column level encryption is meant to protect the data in case the physical media (like drives or backup tapes) are stolen, allowing bad guys to restore or attach the database and steal data. Whereas TDE and column level encryption actually change and scramble the backend data, data masking only hides the data in the query results for end users.

Data is not masked on the physical media or storage device, rather it simply obscures the output or the display of the data, based on whether the user is normal or privileged. Data masking hides the sensitive data in the result set or output of a query over designated database fields, however the data in the database is not changed. Because data masking rules are applied only to the query results, many applications can mask sensitive data without having to modify existing queries. In addition to using this feature in conjunction with best practices such as encryption, security is further enhanced by not allowing users to execute ad hoc queries, as the data can be easily unmasked by using a CAST statement.

Pros                                                                                                             

Benefits of the data masking feature:

  • Developers can test environments using real (albeit, masked) data as opposed to scrubbed data, which can often skew query results. And by using the appropriate data mask to protect personally identifiable information (PII) data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.
  • End users (normal users) are prevented from seeing sensitive data, which can be compliant with policy or simply considered best practice.
  • Creating a mask on a column doesn’t prevent updates
  • SELECT INTO or INSERT INTO to move data from masked column to another table will result in masked data in the target table
  • Backup files retain masked data, and importing a database with masked data will retain masked status
  • Modifications to applications not necessary to read data

Cons                                                                                                            

Disadvantages of data masking:

  • Data is completely viewable when querying tables as a privileged user (non read-only user).
  • Masked data can be completely viewable when querying from a temp table
  • Masking can be ‘unmasked’ by using a CAST statement in an ad hoc query
  • Masking cannot be used for the following types of columns: Encrypted (Always Encrypted), FILESTREAM, or COLUMN_SET
  • It is not useful for protecting data from theft or hacking…it simply hides data when viewing through an end user application or a select statement executed by a read only user

There are four ways to mask the data: DEFAULT(), EMAIL(), PARTIAL(), and RANDOM(). Each of these produces different masked values for different data types. PARTIAL() allows you to customize the masking somewhat with a prefix and suffix of the actual data and padded string for character data. For full explanation of defining your data mask, go here https://msdn.microsoft.com/en-us/library/mt130841.aspx

In order to make the dynamic data masking feature work when connecting through an end user application, use security enabled connection string rather than the original one. The following is a link for connecting to a database through the security enabled connection string: https://www.connectionstrings.com/sql-server/ 

Data Masking Example                                                                                                     

You can mask data in your table columns by either creating your table with masked columns, or altering an already existing table for masking. Below is an example of masking data in an existing table.

— alter existing table column

USE AdventureWorks2014

GO

ALTER TABLE HumanResources.Employee

ALTER COLUMN NationalIDNumber

ADD MASKED WITH (FUNCTION = ‘PARTIAL(0, “xxx-xx-“, 4)’)

–create a Read Only user 

CREATE USER Maryann WITHOUT LOGIN;

GRANT SELECT ON HumanResources.Employee TO Maryann;

You can see below when the select statement is run as a privileged user, all characters are visible in the query results.

SELECT * FROM HumanResources.Employee   — this will show clear data – run as privileged user

Blog_20160218_1

But when the select statement is run as the read-only user as shown below, the sensitive data is masked in the query results.

–run select statement as the read only user

EXECUTE AS USER = ‘Maryann’

SELECT * FROM HumanResources.Employee

REVERT

Blog_20160218_2

Below is an example of a masked email address, selected as a read-only user:

Blog_20160218_3

Conclusion                                                                                                            

The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. With today’s FERPA and HIPAA laws, along with a growing concern over identity theft and security, data masking provides an extra layer of privacy and security. Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security…) and it is highly recommended to use this feature in conjunction with them.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL Connections – Finding what fixed port SQL is listening on

— by Lori Brown  @SQLSupahStah

While trying to add a database to an existing AlwaysOn Availability Group (AG), I was having difficulty making a connection from the primary replica to the secondary replica.

Blog_20160212_1

The availability group was functioning but as I tried to add a database to it, my connection to the secondary replica was being rejected with the following error:

Blog_20160212_2

That was interesting since I know the replica is up.  So I also tested connecting from the primary to the secondary from Management Studio with the same result.  Normally when I see the message above I would immediately solve my connection issue by turning on the Named Pipes protocol in the Configuration Manager.  However, in this case the AG was functioning so it had to be communicating without Named Pipes being necessary and I did not have a maintenance window to restart SQL since this is a 24×7 shop.

I decided that there must be a port that SQL is using that is not the normal 1433.  So, I found the sys.dm_tcp_listener_states dmv that gave me the info that I was looking for.  In the below table, I could see that the AG listener was using port 5022 as it should and SQL was using 1434 and another port.  That had to be the port I was looking for!

SELECT * FROM sys.dm_tcp_listener_states

Blog_20160212_3

Since I had just taken over these boxes, I had no idea that they were not listening on the regular port so I confirmed my findings in the properties of the TCP/IP protocol for the instance.

Blog_20160212_4

When TCP Dynamic Ports has no value, SQL will listen on the TCP port you fill in for it.

Blog_20160212_5

Once I connected to the secondary replica in management studio, I was then able to connect to the secondary replica in the Add Database wizard for AlwaysOn.

More info on troubleshooting and setting ports can be found at:

sys.dm_tcp_listener_states – https://msdn.microsoft.com/en-us/library/hh245287.aspx

Configure a Server to Listen on a Specific TCP Port – https://msdn.microsoft.com/en-us/library/ms177440.aspx

Troubleshoot AlwaysOn Availability Groups Configuration – https://msdn.microsoft.com/en-us/library/ff878308.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Feb 2016 Tip of the Month – New function in SQL 2016: sys.dm_exec_function_stats

SQL Server Performance Tuning:  New function in SQL 2016: sys.dm_exec_function_stats

Returns performance statistics for cached functions. Returns information about scalar functions, including in-memory functions and CLR scalar functions but not about table valued functions. The view returns one row for each cached function plan, and the lifetime of the row is as long as the function remains in memory. When a function is removed from the cache, the corresponding plan is no longer available in the view.

SELECT [object_id],

[database_id],

OBJECT_NAME(object_id, database_id) ‘function name’,

cached_time,

       last_execution_time,

total_elapsed_time,

total_elapsed_time/execution_count AS [avg_elapsed_time],

last_elapsed_time,

       execution_count,

total_physical_reads,

total_logical_writes,

total_logical_reads,

total_worker_time

FROM sys.dm_exec_function_stats AS

ORDER BY [total_logical_reads] DESC

 

More information from Microsoft can be found here: https://msdn.microsoft.com/en-us/library/mt429371.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!