the data file, log file or both.
Thursday, 4 November 2021
Full Backup Vs. Differential Backup Vs. Log Backup
the data file, log file or both.
Monday, 1 November 2021
Removing Invalid Logins (Both Server and Database Principals) from SQL Server
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins
- Whether the user owns a schema in the database (if so we need to change the schema ownership to dbo)
- Whether the user owns a the database. If so we need to change db ownership to a different user. In this example I will be changing the ownership to "sa"
We will insert the details into another temporary table. I will explain part by part, but once the script is completed you can execute it as a whole. I have provided the completed script at the end.
We will use another system stored procedure to execute a query in all the databases in the SQL Server instance (sys.sp_MSforeachdb)
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_2
DECLARE @SqlSyntax AS NVARCHAR(MAX) = '' SET @SqlSyntax += CONCAT(' --== Changing Database Ownership ==--',CHAR(13),CHAR(13),'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME ,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13) ) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins ------------------------------------------------------------------------------------------ CREATE TABLE #InvalidUserDbInfo( DBNAME NVARCHAR(128) ,[SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ,OWNED_SCH VARCHAR(128) ) DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ SET @SqlSyntax += CONCAT(CHAR(13),CHAR(13) ,' --== Changing Database Ownership ==--',CHAR(13),CHAR(13) ,'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13)) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3
Sunday, 31 October 2021
Attaching SQL Database with FILESTREAM data
Have you ever come across a situation where you need to attach a database using existing files (mdf, ldf & file stream data), but you don't have an option to mention the file stream folder when you use SSMS.
This won't be an issue if the filestream folder resides on its original path.
Let's look at an example. I have SQL data, log and filestream data which I will first attach using SSMS.
- Right-click on the database folder in SSMS and choose "Attach"
- Click the "Add" button and choose the data file (.mdf). Upon selecting the mdf file it will list the associated ldf file as well. But make sure to change the path accordingly from the second pane.
- Click on Ok.
Even if you try to generate the scripts using SSMS it doesn't provide you with the option to include the filestream folder.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON ( FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf' ), ( FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf' ) FOR ATTACH GO
We can use the above code snippet and include the file stream path.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON (FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf'), (FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf'), FILEGROUP NewFSGroup CONTAINS FILESTREAM ( NAME = 'NewDatabaseFileStream' ,FILENAME = 'D:\_SQL_DATA\SampleSqlDatabase_FSData_New' ) FOR ATTACH GO
Then you will be able to attach the database without an issue. You can use any name for the FILEGROUP and NAME (under the FILEGROUP). It doesn't need to be the same as your source database.
Hope this will be a helpful tip to you.
SQL Server Transaction Log file behaviour in Full Recovery Mode
In a previous blog post, I have explained different types of recovery models in SQL Server databases. During the explanations, I have stated that the transaction log data will be truncated during certain backup types. We will look into this more closely and see how it works.
Note: Even the details are removed from the transaction log file, the physical size may not change unless you issue a DBCC SHRINKFILE command. Only the file space will be re-used once the details are truncated.
We will start with creating a sample database named 'SqlTransactionLogExample'
USE master; GO CREATE DATABASE [SqlTransactionLogExample]; --== Makesure the database is in "Full" recovery model ==-- SELECT [name] ,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model FROM sys.databases WHERE [name] = 'SqlTransactionLogExample'
We will create one table and insert records (1,000,000 rows to see a significant change in the file size).
USE SqlTransactionLogExample;
GO
CREATE TABLE dbo.UidInformation(
Id UNIQUEIDENTIFIER NOT NULL
,PayLoad CHAR(1000) NOT NULL CONSTRAINT [DF_SampleTable] DEFAULT (SPACE(1000))
)
INSERT INTO dbo.UidInformation (
Id
,PayLoad
)
SELECT
NEWID() AS Id,
([100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num) AS PayLoad
FROM (
SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 0
) AS [1]
CROSS JOIN (
SELECT 10 AS Num UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 UNION ALL
SELECT 0
) AS [10]
CROSS JOIN (
SELECT 100 AS Num UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
UNION ALL SELECT 0
) AS [100]
CROSS JOIN (
SELECT 1000 AS Num UNION ALL SELECT 2000 UNION ALL SELECT 3000
UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000
UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
UNION ALL SELECT 0
) AS [1000]
CROSS JOIN (
SELECT 10000 AS Num UNION ALL SELECT 20000 UNION ALL SELECT 30000
UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000
UNION ALL SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
UNION ALL SELECT 0
) AS [10000]
CROSS JOIN (
SELECT 100000 AS Num UNION ALL SELECT 200000 UNION ALL SELECT 300000
UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000
UNION ALL SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
UNION ALL SELECT 0
) AS [100000]
WHERE(
[100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num
) BETWEEN 1 AND 1000000
ORDER BY ([100000].Num + [10000].Num + [1000].Num + [100].Num + [10].Num + [1].Num)
Now we will inspect the number of entries in the transaction log.
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 2511475
Note: Record count may be varied in the transaction log of your database.
We will check the size of the transaction log file
Now we will take a full database backup
USE master; GO BACKUP DATABASE [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_FullDB_Backup.bak' Now we will inspect number of entries in the transaction log file again. USE SqlTransactionLogExample GO SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 2511475
Note: There is a slight increment of the record count since there are new entries written to the log file during the database backup.
We will now inspect the transaction log entries
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) -- Returns 2511545
We will now take a backup of the transaction log
BACKUP LOG [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_LogBackup_AfterInsert.bak'
And if we inspect the number of entries in the log file it has drastically reduced.
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 10
However, the space consumed by the log file is still the same. This is the reason I have mentioned it previously. In order to release the space, we have to issue the DBCC SHRINKFILE command.
--This is to find the name of the logical log file name SELECT * FROM sys.sysfiles --SqlTransactionLogExample_log DBCC SHRINKFILE('SqlTransactionLogExample_log',1)
Hope this will be helpful to you.
Saturday, 30 October 2021
Recovery Models in SQL Server Databases
"Recovery Model" determines how long the data is to be kept in the transaction logs. It also allows what sort of backups and restores you can perform on the database.
Types of recovery models
There are three types of recovery models in SQL Server
- Simple
- Full
- Bulk-logged
How can I change the recovery model of a database?
To change using T-SQL statement use the following syntax:
USE master; ALTER DATABASE YourDBName SET RECOVERY SIMPLE
SELECT [name] ,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model FROM sys.databases WHERE database_id > 4
Simple Recovery Model
- You cannot take backups of the transaction log explicitly
- A database backup will always contain both data and transaction log file
- Transaction log will not grow beyond what's been defined. The space in the transaction log file will be re-used (overwritten) once the transactions are written to the data file.
- In case of a disaster transaction occurred between the last full backup and the disaster timeline cannot be recovered. (E.g: Assume we have a database in which the backups are taken every hour. (8am, 9am etc.) In case a disaster happens at 10:45am, transactions between 10am and 10:45am will be lost)
Bulk-logged Recovery Model
- You can take the backup of the transaction log explicitly
- Log truncation won't happen once the transaction log backup is taken. Hence it needs to be truncated manually (Same as the "Full" recovery model)
- In the bulk-logged recovery model, certain operations are logged minimally. E.g: Bulk import operations such as BCP operations and BULK INSERT, SELECT INTO operations. More details on such operations can be found on the following link: https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged
Full Recovery Model
- Supports transaction log backup
- Chances of a data loss are minimal (subject to the backup strategy implemented)
- Log truncation must be done manually (Upon taking the transaction log backup, it will mark unused space which will be overwritten later by future transactions)
- Transaction log could grow large compared to the "Simple" recovery model
Friday, 10 May 2019
Strange behavior on JSON_VALUE when table contains blank and non-blank values (JSON text is not properly formatted. Unexpected character '.' is found at position 0.)
Few days back we had a requirement to search whether a certain value exists in one of the table fields where the values are stored as JSON strings. The default constructor has been set up not to allow any NULLs but in case there’s no value, the default value has been setup as an empty string.
So basically the query would be similar to something shown below
SELECT 'x' FROM [schema].[TableName] AS Src WHERE JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'
How ever when we ran this query we got the following error
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
Initially we thought that we have typed the attribute incorrectly since it’s case sensitive. But in this case it was correct.
We investigated further and found out few things. But prior explaining them we will replicate this issue. For this I will create one simple table and insert three records.
--== Create a table ==-- CREATE TABLE dbo.Employee_Information ( Id INT ,FirstName NVARCHAR(100) ,LastName NVARCHAR(100) ,JsonData NVARCHAR(MAX) ) --== Insert few rows ==-- INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}') ,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}') ,(3,'Luke','Skywalker','')Now we will use the following query to find any records which the LastName is like ‘Doe’.
SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
**Note : The query will return results till the error occurs. Hence you will see some rows in your result tab in SSMS.
These are the observations we made during our investigation
Observation 01
If you query the table with a predicate and if that predicate doesn’t include any rows with blank values in the JSON (it’s an NVARCHAR column) field the query will executed successfully.
--== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id IN (1,2) --== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id <> 3 --== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id = 3
Observation 02
Even you use a filter to fetch only rows containing a valid JSON the execution will be successful.
--== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE ISJSON(E.JsonData) > 0 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 03
Even you use a filter to fetch only rows containing a non-blank value in the JSON field, it will fail.
--== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE E.JsonData <> '' AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 04
If you remove records and only keep either one type of rows (either only blank rows or only non-blank) the query will be executed successfully.
TRUNCATE TABLE dbo.Employee_Information INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}') ,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}') --== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 05
If you have rows only containing blank values in the JSON field the query will fail.
TRUNCATE TABLE dbo.Employee_Information INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','') ,(2,'Jane','Doe','') --== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Hope this might help you if you encounter this strange behavior during your development.
Note : All the above queries are executed under the following SQL Server Version (SELECT @@VERSION)
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2012 Standard 6.2
Monday, 10 September 2018
Applying database principal through out the server (for all databases) for a particular user
Ever come across a requirement which you required to give db_datareader access to a specific user across all the databases on a particular SQL Server. The task is simple as long as you don’t have many databases in the same SQL Server. How ever if the number of databases are very high this can be a very time consuming one.
This can be done either using the GUI (SSMS) or using a T-SQL script. We will consider both options.
Using SQL Server Management Studio
In order to illustrate this we will create a SQL Login ‘db_user_read_only’ with ‘public’ server role and on the user mapping, we will apply the db_datareader principal.
Like mentioned it would be easy to use the GUI when you have less number of databases. But if the SQL Server contains lots of databases this will be a very time consuming job. Then it would be very handy to use the latter approach.
Using T-SQL
You can use the following script to apply the db_datareader principal across all the databases on a particular server.
DECLARE @Sql AS NVARCHAR(MAX) ,@UserId AS VARCHAR(MAX) = 'YourLoginId' SET @Sql = CONCAT(' USE [?]; IF EXISTS (SELECT 0 FROM sys.database_principals AS DP WHERE name = ''',@UserId,''') BEGIN EXEC sys.sp_change_users_login ''update_one'',''',@UserId,''',''',@UserId,''' END ELSE CREATE USER [',@UserId,'] FOR LOGIN [',@UserId,'] ALTER ROLE [db_datareader] ADD MEMBER [',@UserId,'] ') EXEC sys.sp_MSforeachdb @command1 = @Sql ,@replacechar = '?'
Please note the following:
- On the above code I haven’t excluded the system databases.
- If the login exists on the database it will map the database user using sp_change_users_login
Hope this might be very useful to you.
Friday, 6 July 2018
Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017
Alternatively Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.
You can get further details on the aforementioned view by visiting the link. (links are embedded into the view name)
However if you have used sp_depends you might have already faced the issue that the results which is being returned from this stored procedure is not very accurate (most of the time it seems fine)
Otherday I was going through these two view in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.
DECLARE @objname AS NVARCHAR(100) = 'Website.SearchForPeople' ,@objclass AS NVARCHAR (60) = 'OBJECT' SELECT CONCAT(sch.[name],'.',Obj.[name]) AS [name] ,(CASE Obj.type WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'PK' THEN 'PRIMARY KEY constraint' WHEN 'R' THEN 'Rule (old-style, stand-alone)' WHEN 'TA' THEN 'Assembly (CLR-integration) trigger' WHEN 'TR' THEN 'SQL trigger' WHEN 'UQ' THEN 'UNIQUE constraint' WHEN 'AF' THEN 'Aggregate function (CLR)' WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'FN' THEN 'SQL scalar function' WHEN 'FS' THEN 'Assembly (CLR) scalar-function' WHEN 'FT' THEN 'Assembly (CLR) table-valued function' WHEN 'IF' THEN 'SQL inline table-valued function' WHEN 'IT' THEN 'Internal table' WHEN 'P' THEN 'SQL Stored Procedure' WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' WHEN 'PG' THEN 'Plan guide' WHEN 'PK' THEN 'PRIMARY KEY constraint' WHEN 'R' THEN 'Rule (old-style, stand-alone)' WHEN 'RF' THEN 'Replication-filter-procedure' WHEN 'S' THEN 'System base TABLE' WHEN 'SN' THEN 'Synonym' WHEN 'SO' THEN 'Sequence OBJECT' WHEN 'U' THEN 'Table (user-defined)' WHEN 'V' THEN 'VIEW' WHEN 'SQ' THEN 'Service queue' WHEN 'TA' THEN 'Assembly (CLR) DML trigger' WHEN 'TF' THEN 'SQL table-valued-function' WHEN 'TR' THEN 'SQL DML trigger' WHEN 'TT' THEN 'Table type' WHEN 'UQ' THEN 'UNIQUE CONSTRAINT' WHEN 'X' THEN 'Extended stored procedure' ELSE 'Undefined' END) AS [type] ,Obj.create_date ,Obj.modify_date ,src.referenced_minor_name AS [column] ,IIF(src.is_selected = 1,'yes','no') AS is_selected ,IIF(src.is_updated = 1,'yes','no') AS is_updated ,IIF(src.is_select_all = 1,'yes','no') AS is_select_all ,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all FROM sys.dm_sql_referenced_entities (@objname,@objclass) AS src JOIN sys.objects AS Obj ON src.referenced_id = Obj.[object_id] JOIN sys.schemas AS Sch ON Sch.[schema_id] = Obj.[schema_id] WHERE 1=1 SELECT CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name] ,(CASE Obj.type WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'PK' THEN 'PRIMARY KEY constraint' WHEN 'R' THEN 'Rule (old-style, stand-alone)' WHEN 'TA' THEN 'Assembly (CLR-integration) trigger' WHEN 'TR' THEN 'SQL trigger' WHEN 'UQ' THEN 'UNIQUE constraint' WHEN 'AF' THEN 'Aggregate function (CLR)' WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'FN' THEN 'SQL scalar function' WHEN 'FS' THEN 'Assembly (CLR) scalar-function' WHEN 'FT' THEN 'Assembly (CLR) table-valued function' WHEN 'IF' THEN 'SQL inline table-valued function' WHEN 'IT' THEN 'Internal table' WHEN 'P' THEN 'SQL Stored Procedure' WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' WHEN 'PG' THEN 'Plan guide' WHEN 'PK' THEN 'PRIMARY KEY constraint' WHEN 'R' THEN 'Rule (old-style, stand-alone)' WHEN 'RF' THEN 'Replication-filter-procedure' WHEN 'S' THEN 'System base TABLE' WHEN 'SN' THEN 'Synonym' WHEN 'SO' THEN 'Sequence OBJECT' WHEN 'U' THEN 'Table (user-defined)' WHEN 'V' THEN 'VIEW' WHEN 'SQ' THEN 'Service queue' WHEN 'TA' THEN 'Assembly (CLR) DML trigger' WHEN 'TF' THEN 'SQL table-valued-function' WHEN 'TR' THEN 'SQL DML trigger' WHEN 'TT' THEN 'Table type' WHEN 'UQ' THEN 'UNIQUE CONSTRAINT' WHEN 'X' THEN 'Extended stored procedure' ELSE 'Undefined' END) AS [type] ,Obj.create_date ,Obj.modify_date FROM sys.dm_sql_referencing_entities (@objname,@objclass) AS Src JOIN sys.objects AS Obj ON Obj.[object_id] = Src.referencing_idI have even compiled a stored procedure using this syntax and it can be found on the following reporsitory: https://github.com/manjukefernando/sp_depends_v2
Wednesday, 30 May 2018
Computed columns in SQL Server
Why do we need computed columns ?
First we will consider a case where we need to store details on a table without the usage of computed columns.Consider we have a table which contains employee details. We have two columns to store employee’s first and last names. But we also required to have a column which we need to store their full name as well by concatenating the first and last names. So the correct way is to have the third column which contains the full name and the data needs to be inserted during the employee record is created and it should be maintained in the case where the details are updated as well. Otherwise the data integrity will be lost. (One might debate that the full name can be built from the business logic code using the first and last names. But for the illustration purpose we would consider that we are maintaining it using SQL Server)
CREATE TABLE dbo.Employee( Id INT ,FirstName VARCHAR(30) ,LastName VARCHAR(30) ,FullName VARCHAR(61) )How ever we could achieve the same with the use of a computed column and with a less effort compared to the first approach.
CREATE TABLE dbo.Employee( Id INT ,FirstName VARCHAR(30) ,LastName VARCHAR(30) ,FullName AS CONCAT(FirstName,' ',LastName) )
Let’s insert few records to the table which we created now.
INSERT INTO dbo.Employee(Id, FirstName, LastName) VALUES (1,’John’,’Doe'),(2,’Jane’,’Doe')
PERSISTED, DETERMINISTIC or NON-DETERMINISTIC ?
The values reflected on computed column can be either deterministic or persisted.CREATE TABLE dbo.Employee2( Id INT ,FirstName VARCHAR(30) ,LastName VARCHAR(30) ,CreatedDate AS GETDATE() ) INSERT INTO dbo.Employee2(Id, FirstName, LastName) VALUES (1,'John','Doe')
And when queried the calculated column returns different values as shown below.
https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017
We will drop ‘FullName’ column and recreate the column.
ALTER TABLE dbo.Employee DROP COLUMN FullName; ALTER TABLE dbo.Employee ADD FullName AS CONCAT(FirstName,' ',LastName) PERSISTED;**Note: If you try to drop the ‘CreatedDate’ column on Employee2 and try to create it as PERSISTED, it will throw an error. Because computed columns can only be persisted when it’s deterministic.
Msg 4936, Level 16, State 1, Line 45
Computed column 'CreatedDate' in table 'Employee2' cannot be persisted because the column is non-deterministic.
Now when the expression is evaluated during the execution, the ‘FullName’ will be saved into the table.
The data is read-only to the developer and it’s maintained by the engine. When the data is changed on the columns which was used in the formula, the computed values will be changed.
Tuesday, 20 March 2018
Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)
Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.g: a bunch of products, transactions such as invoices, receipts etc. or customer details.
But today data has become an important entity, which drives business towards success. In today’s fast-moving world, companies who owned data and does analytics has become the most successful companies.
However one of the major concerns we have today is how to protect these data. Especially the sensitive ones. Since more data is being exposed to the cloud, it’s essential to protect it from going to the wrong hands and it has become a major problem since hackers nowadays are well equipped and are always on the look for stealing this valuable information whenever possible, since it’ll be a valuable asset in the open market.
But protecting the data from unauthorized access is a must. Failing to do so can have unexpected consequences. Entire business could get wiped out of the business due to this. Hence enterprises should seriously consider protecting their data and we will discuss how we can achieve this in SQL Server through data encryption.
Ways of Data Encryption in SQL Server
There are few ways of encrypting data in SQL Server. We will discuss the advantages and disadvantages of each method.
SQL Server provides following methods to encrypt data:
- T-SQL Functions
- Using Symmetric Keys**
- Using Asymmetric Keys**
- Using Certificates**
- Transparent Data Encryption**
**Note : In this article I only plan to explain encryption/decryption functionality using T-SQL. I will talk about other methods which is mentioned about in future articles.
Using T-SQL Functions
Encrypting data using ENCRYPTBYPASSPHRASE
Encryption is done using T-SQL function ENCRYPTBYPASSPHRASE.
ENCRYPTBYPASSPHRASE(passphrase,text_to_encrypt)
The first parameter is the passphrase which can be any text or a variable of type NVARCHAR, CHAR, VARCHAR, BINARY, VARBINARY, or NCHAR. The function uses this passphrase to generate a symmetric key.
For the illustration purpose we will create a table which to hold employee details
CREATE TABLE dbo.Employee( Id INT ,EmpName VARCHAR(100) ,EmpDOB SMALLDATETIME ,SSN VARBINARY(128) )
This example is to demonstrate the data encryption during INSERT DML Statement
INSERT INTO dbo.Employee( Id ,EmpName ,EmpDOB ,SSN ) VALUES( 1 ,'Luke' ,'01-June-1980' ,ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','111-22-3333') )
Further details can be found in the Microsoft Documentation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql
Decrypting data using T-SQL function DECRYPTBYPASSPHRASE
Will take the same details which we inserted during the first case. The encrypted data can be decrypted using SQL function DECRYPTBYPASSPHRASE. If any attempt has been made to decrypt the data without using DECRYPTBYPASSPHRASE nor providing the proper details, it will fail the operation.
Without Decryption
SELECT Id,EmpName,EmpDOB,CONVERT(VARCHAR(128),SSN) AS SSN FROM dbo.Employee WHERE Id = 1
With Decryption (Incorrect Pass-phrase)
SELECT Id ,EmpName ,EmpDOB ,DECRYPTBYPASSPHRASE('IncorrectPassword',SSN ) AS SSN FROM dbo.Employee WHERE Id = 1
But providing the correct pass-phrase will return the correct details
SELECT Id ,EmpName ,EmpDOB ,CONVERT(VARCHAR(128),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',SSN )) AS SSN FROM dbo.Employee WHERE Id = 1
However there could be a requirement which you need to protect your data, not from stealing, but from getting updated with someone else’s.
One classic example is a login table. Suppose we have a table which stores login credentials, which is having the following structure.
*Note: In real world cases, usually it’s more secure if you hash passwords rather than encrypting them. But I am using encryption for illustration purpose.
So if a person has access to update the details on the password column, he/she can easily replace the contents with their own and log on using that. This can be stopped by providing two additional values when details are inserted to the table using ENCRYPTPASSPHRASE.
CREATE TABLE dbo.LoginCredentails( UserId INT ,UserName VARCHAR(20) ,Pwd VARBINARY(128) )
We will insert two records to the above created table.
INSERT INTO dbo.LoginCredentails( UserId ,UserName ,Pwd ) VALUES (1001,'luke.skywalker',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','force be with you',1,CAST(1001 AS sysname))) ,(1002,'darth.vader',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','i am your father',1,CAST(1002 AS sysname)))
Please note that unlike the previous example, we are now providing two additional values to the ENCRYPTBYPASSPHRASE function. The first values is 1, which indicates whether whether an authenticator will be encrypted together with the password. If the value is 1 and authenticator will be added. The second value is the data which from which to derive an authenticator. In this example we will use a value similar to a user id, so that when the value is decrypted, we could use the same value.
Following is a function to fetch the decrypted password based on the UserId. Assume we will be using this when validating the credential prior login.
CREATE FUNCTION Fn_GetUserPwdById(@UserId AS INT) RETURNS VARCHAR(50) AS BEGIN DECLARE @Pwd AS VARCHAR(50) SELECT @Pwd = CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',LC.Pwd,1,CAST(LC.UserId AS sysname))) FROM dbo.LoginCredentails AS LC WHERE LC.UserId = @UserId RETURN @Pwd END
Using the aforementioned function we will retrieve the details.
SELECT UserId ,UserName ,dbo.Fn_GetUserPwdById(UserId) AS Pwd FROM dbo.LoginCredentails
But querying the data simply will get you the binary string of the encrypted value.
SELECT UserId ,UserName ,Pwd FROM dbo.LoginCredentails
Suppose if a person has enough privileges to do an update the password with a known one (from an existing user) it’ll allow him/her to login to the system impersonating any user.
UPDATE LC SET LC.Pwd = ( SELECT LC2.Pwd FROM dbo.LoginCredentails AS LC2 WHERE LC2.UserName = 'luke.skywalker' ) FROM dbo.LoginCredentails AS LC WHERE LC.UserName = 'darth.vader'
But if when the same function is used for decryption, it will return NULL for the updated record, preventing the login to be invalid if it’s replaced using an existing one.
Apart from the above mentioned function, there’s another function which can be used to hash data. Unlike encrypting, there’s no way you can reverse the hashed data and see the raw details.
Syntax:
HASHBYTES ( 'algorithm', { @input | 'input' } ) /* algorithm::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 */
There are two parameters which you require to provide. The first parameter is the algorithm which should be used for hashing. The hashing algorithm can be any of the following:
- MD2
- MD4
- MD5
- SHA
- SHA1
- SHA2_256
- SHA2_512
The second parameter is the input, which needs to be hashed. This can be either a character or binary string.
The return value is VARBINARY(n). n = maximum 8000 bytes.
Example:
DECLARE @TextToHash AS NVARCHAR(1000) = N'My Secret Message' SELECT HASHBYTES('SHA1',@TextToHash) AS HashedData
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql
Hope this might be useful to you and please feel free to comment your ideas.
Saturday, 10 March 2018
Strange behaviour converting NVARCHAR(MAX) to BINARY
Few days back I was writing a CLR function to be used for hashing string values. The only option was the CLR functions since T-SQL doesn’t have any functionality to convert a string to hashed value using a key. Using the HASHBYTES function you can only provide the algorithm.
DECLARE @Data NVARCHAR(4000); SET @Data = CONVERT(NVARCHAR(4000),'My Secret Message'); SELECT HASHBYTES('SHA1', @Data);
I have written the CLR function to achieve the requirement, but during testing the validation was failing and when I go through the code I couldn’t find any issue in the function as well. But inspecting carefully I noticed that when a variable type NVARCHAR(n) and a variable type of NVARCHAR(MAX) gives different results when it’s converted to Binary. Which was the root cause for the issue I was facing.
DECLARE @Data1 AS NVARCHAR(MAX) = '1111' ,@Data2 AS NVARCHAR(10) = '1111' SELECT CAST(@Data1 AS BINARY(30)) AS ValueMax SELECT CAST(@Data2 AS BINARY(30)) AS ValueN
As you can see the above example the zero bytes are represented differently for NVARCHAR(MAX) when it’s converted to BINARY.
I do not have any explanation for this. I am sharing the information in case anyone come across this issue. Please feel free to comment.
Sunday, 3 December 2017
Behaviour of IDENTITY Columns and SEQUENCES with TRANSACTIONS
Few days back, I was caught in a discussion with couple of my colleagues, regarding a problem they are facing with an IDENTITY column.
The issue was that when a transaction is rolled back the identity seed isn’t rolling back as expected. This was causing the business application to loose the id sequence.
There is no fix or a workaround for this. All that I could provide was an explanation.
I will illustrate the issue and an explanation why it’s happening.
Behaviour of IDENTITY Columns
We will create the following table to hold employee details.
CREATE TABLE dbo.EmployeeInfo( Id INT IDENTITY(1,1) NOT NULL, EmpName VARCHAR(100) NOT NULL )
Now we will insert few records to the table in the following manner.
- Without a transaction
- With a transaction. But we will rollback the transaction.
- With a transaction. But we will commit it.
INSERT INTO dbo.EmployeeInfo (EmpName) VALUES('John') BEGIN TRAN INSERT INTO dbo.EmployeeInfo (EmpName) VALUES('Jane') ROLLBACK INSERT INTO dbo.EmployeeInfo (EmpName) VALUES('James') SELECT EI.Id ,EI.EmpName FROM dbo.EmployeeInfo AS EI
And when checked, you could see the following results.
Usually the expectation is to see the employee “James” with an Id of 2.
What you should understand here is that this isn’t a flaw or a bug. This is the exact intended behaviour and it has been explained in the following MSDN article.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property
Behaviour of SEQUENCES
SEQUENCEs were introduced in SQL Server 2012. The purpose of the SEQUENCE objects were to aid in handling the auto increment numbers, in case you prefer to handle the sequence without using an IDENTITY column.
First we will create a sequence object. The minimum syntax required to create a sequence object is a name and the data type. Additionally you can mention many other attributes like starting index, increment seed etc.
CREATE SEQUENCE dbo.TempNumberSequence AS INT
Further details regarding other options can be found on the following URL:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql
Now we will create a similar table like we created in the previous example, but without an IDENTITY column.
CREATE TABLE dbo.EmployeeInfoSeq( Id INT ,EmpName VARCHAR(100) NOT NULL )
We will insert 3 records in the same way like we did in the previous example.
DECLARE @NextSeq AS INT SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence INSERT INTO dbo.EmployeeInfoSeq ( Id ,EmpName ) VALUES ( @NextSeq ,'John' ) GO DECLARE @NextSeq AS INT SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence BEGIN TRAN INSERT INTO dbo.EmployeeInfoSeq ( Id ,EmpName ) VALUES ( @NextSeq ,'Jane' ) ROLLBACK GO DECLARE @NextSeq AS INT SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence INSERT INTO dbo.EmployeeInfoSeq ( Id ,EmpName ) VALUES ( @NextSeq ,'James' ) GO
Afterwards if you check, you will see the following results.
Hope this will help you in you day to day development work.
Tuesday, 18 October 2016
Always Encrypted feature in SQL Server 2016
With the introduction of SQL Server 2016 in June (Official Final Release), Microsoft had introduced few, new and very useful features in to the SQL Server. One such feature is the ‘Always Encrypted’.
‘Always Encrypted’ is the ability to perform SQL operations (there are restrictions) on your data as it were normal (non encrypted), while keeping them encrypted all the time. This means SQL Server will always get encrypted data to be stored into the tables. This will put an extra layer of protection on to your data making sure that even onsite DBA’s or Developers cannot see the plain text value behind the encrypted data using their level of access. (Users with ‘SysAdmin’ access won’t be able to see these details without the Key). Therefore ‘Always Encryption’ provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).
Why Always Encrypted ?
There are many benefits using Always Encrypted feature:
- It provides a clear separation between the data owners and people who manage it
- Unless proper access is provided via encryption keys, even DBA’s or SysAdmin users cannot access the data in plain text
Ultimately aforementioned points will provide an unparalleled protection against data breaches and help to protect sensitive information such as credit card numbers, personal details etc. Also this will broaden the boundaries where such sensitive information can be kept.
How Always Encrypted Works ?
This is a client-side encryption technology which the SQL Server Client Driver plays a key role.
- The data is transparently encrypted inside a client driver
- Client manages the encryption key. SQL Server doesn’t have any information regarding the encryption key.
SQL Server can query and perform certain computations on the encryption data, such as equality comparisson, equality joins, group by etc.
Always Encrypted Demonstration
We will see how Always Encrypted can be implemented and used. In order to illustrate, we will use a table which contains employee information.
CREATE TABLE Employee( Id INT ,FirstName VARCHAR(100) ,LastName VARCHAR(100) ,DOB DATE ,SSN INT ,[Address] VARCHAR(255) ,PostalCode INT ) INSERT INTO Employee ( [Id],[FirstName],[LastName],[DOB],[SSN],[Address],[PostalCode]) VALUES (1,'James','Rubin','20-Jul-1986',173456858,'10585 N 600 E',46310) ,(2,'Austin','Pyatt','24-Dec-1985',138868248,'100 BENTBROOK CT',27519) ,(3,'Stacey','Munoz','23-Dec-1988',185682639,'1 WOODSIDE DR',4976) ,(4,'James','Tweed','03-Jan-1987',133890886,'1 AUNNEK CT',95023) ,(5,'James','Robles','11-Sep-1989',154135505,'101 FISHTRAP RD',35504) ,(6,'Ebony','Lewis','17-Jul-1988',120488337,'101 N OAKS DR',35180) ,(7,'Marian','Caro','20-Nov-1985',115281829,'1017 FISK ST SE',49507) ,(8,'Lynne','Martinez','22-Apr-1985',157900240,'103 UNITY CT',78214) ,(9,'Elsa','Cole','25-Apr-1990',150631885,'1001 E FERN AVE APT 201',78501) ,(10,'Kiley','Caldwell','03-Jan-1988',131368172,'103 NOB HILL LN APT 5',40206) ,(11,'Michael','Soluri','17-Jun-1985',173245124,'10770 S KILBOURN AVE',60453) ,(12,'Gregory','Emmons','06-Sep-1988',137693229,'10 LOUISA PL APT 2F',7086) ,(13,'Jessica','Barr','04-Feb-1989',155895863,'1 FAWNRIDGE DR',94945) ,(14,'Daniel','Mccabe','06-Sep-1985',148236776,'1 CALLE MARGINAL GARCIA',674) ,(15,'Sharon','Schwartz','06-Sep-1987',117569460,'1 KRITTER CT',8050) ,(16,'Dorthy','Wear','13-Dec-1988',170517705,'1 CLARK RD',35747) ,(17,'Betsy','Blansett','17-Jun-1990',182202498,'10 CALLE 1 DE FLORIDA',612) ,(18,'Margaret','Payne','25-Jul-1985',157359609,'1003 BLOOMFIELD AVE',7006) ,(19,'James','Walker','26-Jan-1989',142829150,'100 CONGLETON HOLLOW SPUR RD',40447) ,(20,'Sarah','Reeves','22-Jun-1990',146171169,'1 BLUEBERRY LN',1832)
I have a small MVC Web Application which has a page to list out the aforementioned details from the SQL Server. The MVC Controller will load the details to a list of Employee records and pass it to the Html view which will be displayed as follows.
In the MVC application I have the following data model to load details from the SQL Database Table.
public class Employee { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime DOB { get; set; } public int SSN { get; set; } public string Address { get; set; } public int PostalCode { get; set; } public Employee() { } }
And I am using the following connection string in order to connect to the SQL Server Database.
const string zConnectionString = @"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;Trusted_Connection=True;";
There are few steps to be followed on both SQL Server and application side (Client Applications) in order to implement and use this feature.
From the SQL Server side, there are few ways to enable the Always Encrypted feature. We will look more details how to use these feature using the wizard.
1. Right click the table which you want to encrypt details and select ‘Encrypt Columns’. This will take you the to wizard.
2. You will get the introduction screen which contains few details about what ‘Always Encrypted’ is all about. Click next and proceed to the next screen.
This is the column selection screen, which allows you to select which columns you want to encrypt and using which Encryption Type. There are two Encryption Types available in SQL Server 2016.
- Deterministic –> Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
- Randomized –> Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
This advice has been included in Microsoft Documentation: Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.
So in our example we will choose DOB & SSN columns for encryption. For DOB we will choose Randomized and for SSN we will choose Deterministic.
Once the encryption type is chosen the wizard should be similar to the screen shown below.
If you look closely, you will be able to see that the Encryption Key combo is disabled. The reason for this is the fact that we haven’t created any Column encryption keys so far. If the keys are created prior to the column selection then you will have the option to choose whether to use an existing key or to generate a new key.
In this illustration, we will use the option which will create a new column encryption key. Click next to proceed to the next step.
3. The next step is the Column Master Key Configuration. A Column Master Key will be used to encrypt and protect the Column Encryption Key, which is used to encrypt the data. We will use the option ‘Auto generated column master key’, which the wizard will generate the key for us. When we are creating a new Master Key, there are two options available, where to store the newly generated key. Clicking on the small info button beside each option will give further details about each option
4. Click next to move to the next step. In this step you can decide whether you require a PowerShellscript to be generated for the encryption process or to proceed with the encryption immediately. In this example we will select the second option and click on the next button.
In this step you will be presented with the steps which will be followed during the data encryption
Click finish to complete the encryption process. Once process is completed click close button.
Now if you check the details on SQL Table you can see that, data in SSN and DOB columns are encrypted.
SELECT * FROM dbo.Employee
If you see the Table creation script for the Employee table now, you could see few changes which has been done by the SQL Server after we enabled the encryption for those two columns.
CREATE TABLE [dbo].[Employee]( [Id] [INT] NULL, [FirstName] [VARCHAR](100) NULL, [LastName] [VARCHAR](100) NULL, [DOB] [DATE] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, [SSN] [INT] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, [Address] [VARCHAR](255) NULL, [PostalCode] [INT] NULL ) ON [PRIMARY]
You can see that it had added the ENCRYPTED WITH clause for those two columns. ENCRYPTED WITH clause consist 3 attributes which are:
- COLUMN_ENCRYPTION_KEY –> CEK_Auto1 since we have chosen the option for SQL to generate a new key.
- ENCRYPTION_TYPE –> Can be either RANDOMIZED or DETERMINISTIC
- ALGORITHM –> This is always AES_256
If you inspect the Always Encrypted keys in the object explorer in SSMS you could see the following meta data for the Master and the Column Encrypted Keys.
Column Encrypted Key – CEK_Auto1
- COLUMN_MASTER_KEY –> Name of the column master key protecting the value of the column encryption key.
- ALGORITHM –> Algorithm used to generate the encrypted value of the column encryption key (RSA_OAEP).
- ENCRYPTED_VALUE –> Encrypted value of the column encryption key. The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm.
For further information please refer to the following url: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/
Column Master Key - CMK_Auto1
- KEY_STORE_PROVIDER_NAME –> Name of a provider for the key store that holds the column master key.
- KEY_PATH –> Key path specifying the location of the column master key in the key store.
For further information please refer to the following url: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/
Now if we try to fetch details without doing anything on the sample .Net Application you will get a similar error like shown below.
Now we will look into the things that we required to change on our application side (Business) in order to retrieve the required information.
1. Make sure that the target framework is version 4.6 or higher.
2. In the Connection String include ‘Column Encryption Setting=enabled’
And I am using the following connection string in order to connect to the SQL Server Database.
const string zConnectionString = @"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;Trusted_Connection=True;Column Encryption Setting=enabled;";
Now if we check the details from our application we can see that DOB and SSN values are fetched as plain text, even though the values are encrypted in the SQL Server.
Hope this will help you to understand the ‘Always Encrypted’ feature in SQL Server 2016 and how to integrate it to an existing application.