Task failed due to following error: Cannot save package to file. Error SQL71561: Error validating element in Always Encrypted Implementation

Problem Summary

While I am trying to configure Always Encrypted for one of my databases, I received an error as below.

Sep 19 2018 02:31:58 [Informational] TaskUpdates: Message:Task: ‘Performing encryption operations’ — Status: ‘Failed’ — Details: ‘Task failed due to following error: Cannot save package to file. The model has build blocking errors:
Error SQL71561: Error validating element [dbo].[ViewTableName]: View: [dbo].[ViewTableName] has an unresolved reference to object [DBName].[dbo].[TableName].

Environment Details

Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)

Observations

In fact, looking at the wizard error log report, I have seen hundreds of error messages similar to above for different elements.

While  investigating, it is found that the error has raised due to the usage of three part reference in one of views. And it is confirmed by Microsoft that its a bug in the Always Encrypted tool that it performs a strict integrity verification for the entire database schema instead of the targeted verification, that means, only for the columns to be encrypted.

Solution/Work around

To solve the problem, you need to make sure the referenced objects are present in your environment. In my case, I copied all dependent databases in testing environment and followed the below steps.

  1. Use Generate Scripts(right click on the database, select Generate Scripts in the Tasks, in the wizard, we can select View) to back up the view.
  2. Drop all the selected views which use the three-part naming conventions.
  3.  Apply always encrypt feature to columns.
  4.  Execute the generated script to re-create the view  if needed

Additional Information

When I tried to replicate the same issue in another environment with the below configuration, the above issue could not be replicated.

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 10 Enterprise 6.3 <X64> (Build 14393: ) (Hypervisor)

Test Scripts


--Create two databases
create Database Test1
create Database Test2

use test1
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

use test2
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

--Create the below view in database Test2
create View vw_test1 as Select Col1, Cast(GetDate() as datetime) as [DATECREATED],   
  Cast(GetDate() as datetime) as [DATELASTMODIFIED],   
  Cast(newid() as uniqueidentifier) as [_IMPORTED]   From Test1.dbo.Testtable

--Apply "Always Encrypted", you will see the mentioned behavior.

Try yourself with the above code and share your comments…

Advertisements

Always Encrypted – A new column level security feature in SQL Server 2016

Always Encrypted (AE) is a new feature introduced in SQL Server 2016 to secure your data in SQL Server at column level. Perhaps, SQL Server has many options to secure the data, the new feature Always Encrypted stands out from the list with unique characteristics – “Always Encrypted”.

Before we get into details about Always Encrypted, let us quickly look at the security features in the SQL Server in comparison as an overview.

AE-Always Encrypted, DDM – Dynamic Data Masking, TDE – Transparent Data Encryption

Why do we call Always Encrypted?

As the name depicts, Always Encrypted feature in SQL Server always ensures your data encrypted, that means, the data at rest and in motion. The encryption and decryption happens at client application using an Always Encrypted driver. This separates the encryption from SQL Server database engine and enforces more security in a better controlled manner.

How do we implement Always Encrypted?

First and foremost action is to install the right version of SQL Server 2016.If you do not have the right version, you will not find the option “Encrypt Columns” in “Task” of the database options. If you are not using SSMS version 13.0.4001.0 or above, you will not be able to see this option in your SSMS.

You can find and download SP1

There are two ways, we can implement Always Encrypted in SQL Server using Wizard and T-SQL. However, we need to know that for existing table/column data, there is no way to implement the AE using T-SQL in SQL Server 2016(SP1). I mentioned the service pack as a caveat because Microsoft may change this behavior in future, but unlikely as of now. For existing data, we need to make sure the AE needs to be implemented using Wizard. It is quite good to be noted there is an option to generate PowerShell script for the existing data to encrypt that can be run later.

Using Wizard

Using T-SQL


/*1*/ CREATE COLUMN MASTER KEY CMK_Auto2
WITH (  
  KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',   
  KEY_PATH = 'CurrentUser/my/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F');
/*2*/ CREATE COLUMN ENCRYPTION KEY AEColumnKey   
WITH VALUES  
(  
COLUMN_MASTER_KEY = CMK_Auto2,   
ALGORITHM = 'RSA_OAEP',   
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F00620032003700610034006100390066006300630033003700660032006300350062003100380030003700320034003900660065003100320038003500630064003400610034003000620038003800660053FD933BC3E3E6E5FFD935F452A5C4113FF56E4D946D78B22A69415FF8EF69D9B3A5541F2463BBC32D06AC88AE95B4CDBBEE7A9D1DD80043D7C900F28917637F4414565CB3F2B29CEEE5C03DF182C4F62395CDAD59A59BFCBD421889DB9EFB2B5250AA597268011B8ACCFFA7A1B5D846BD476BBD8F8239D2681C800E3BCD848485AEC6E69FE76D06D2E213FB36FCBCA5E8B75FE67D21C1C05EB7CF819AD9F96701116A2B642F690455FC7DC48AEEB1825BB20ECD428F910C002EE3D186706E00F76C608EF78FBB147ABA798309092517A39C9C4031B3857C599B238174AA1E8433A649D63D194278B0A4EFBF15DF4E4B5B4468FB73FC8992B3E34606AB306E2E19BADEE4B38288FF77B9A8E45A56BB321091EF0CF3567076ED27D875286CB2232177F610B9A0DAEBFA34ABA9856A094E26E995987AD050D27954DDB08BED9A34C6D19CBE6B2271A7E716C33850DB8781C9D3B762C0920EED57BB9D2BA581F7AC1A46EA55962200FD26405FE31005D413BA5B624E5AF2770377A13EB68FB681242B8B719499175113E84073013BDC6E03E5F82EC070B9151705F1C564106B93E3C7566E41BAD00209AB4587278640FE225F797DD9BB83284E8A674DFC7F48558441E00BC856161FC93A38E337B050915450E7B0ED848CDB63272B65319B26B45119ED081852DEBE53DFF7A6CD21935FC3CBF2C4852AD01CFF0153B76C196F7667  
);
/*3*/ CREATE TABLE AlwaysEncryptedSampleSQL(
  EmpID INT PRIMARY KEY,
  SSN NVARCHAR(15) 
    COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AEColumnKey, 
    ENCRYPTION_TYPE = Deterministic, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL);

Why do we need two encryption keys?

Yes, Always Encrypted uses two keys – Column encryption key and master encryption key. Column encryption key is used to encrypt the value in the column and database engine stores the column encryption key in the SQL Server instance. However, master key is being stored in external key store like Windows Certificate store or Azure Key Vault and database engine will have metadata pointer to the external key store. Master key is responsible to encrypt the column encryption key.

How do we verify the above implementation?

1. Check the master key

SELECT name KeyName,column_master_key_id KeyID,
  key_store_provider_name KeyStore,
  key_path KeyPath
FROM sys.column_master_keys;

2. Check the column key

SELECT name KeyName,
  column_encryption_key_id KeyID
FROM sys.column_encryption_keys;

3. Check the sys.columns

Select name,collation_name,encryption_type_desc, encryption_algorithm_name,column_encryption_key_id 
From sys.columns where object_id in (object_id('AlwaysEncryptedSample'),object_id('AlwaysEncryptedSampleSQL'))

How do the application encrypt and decrypt the value?

Client application uses Always Encrypted driver. I would suggest you to go through “Using Always Encrypted with the ODBC Driver for SQL Server” to understand better the usage. Anyway, this blog post will be followed up with the next post, on which I am currently working on, to understand the performance impact.

What are the different types of encryption in Always Encryption feature?

Yes, AE comes up with two different type of encryption.

1. Deterministic
As the name suggests, this type will always produce the same encrypted value for a given text. Ideally, this may not be a good option for all the keys as a good intruder can easily understand the value by analyzing data pattern as an example, gender, polar questions etc. As the encrypted value for a given text is always same, the encrypted column can very well part of a join, grouping and indexing.

2. Randomized
As the name suggests, it will produce randomized value which will make the encryption more secure than the earlier.

Gotchas!!!!…..

1. There is no straight forward method to implement AE for existing data apart using the wizard. However, wizard can generate PowerShell Script to do the action later.
2. Encryption method – Deterministic is less secure compared to “randomized”.
3. Encryption method – Randomized cannot be part of joins/groups/indexing.
4. INSERT/UPDATE operations are not allowed directly to table unless through the client driver. We will receive the below error message.
Msg 206, Level 16, State 2, Line 5
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'test') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

5. By specifying encryption setting in “Additional Connection Parameters” in SQL Connection window, who has access the encrypted table, can see the actual data. However, the login cannot modify or insert new data.

6. Column encryption changes the collation of the string column to Latin1_General_BIN2
7. Encryption will increase the size of the table

See Also

Please refer Transparent Data Encryption

To know more about other Always Encrypted limitations, please refer Aaron’s blog.

ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.

Problem statement

As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.” often for certain actions like taking database offline/setting single user etc.

Analysis

This is due to other processes locking your current operations.

Resolution

1. Find out the query which is blocking the current operation using the below query
Live monitoring query

2. Kill the process and take the database in SINGLE_USER

KILL 191 --session id from the first query related to the database
USE MASTER
ALTER DATABASE DB905 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3. Take the database offline

ALTER DATABASE db905 SET OFFLINE WITH ROLLBACK IMMEDIATE

DBMIRROR_DBM_EVENT wait type causing blocking in SQL Server

Recently, we observed lots of blocking in our production server showing the wait type as DBMIRROR_DBM_EVENT. To brief, the system is an OLTP system with lots of DML actions, ended up with lot of long running transactions and blocking the user transactions. As DBMIRROR_DBM_EVENT wait type is an internal to Microsoft and there is less documentation available on Web, we started our troubleshooting looking at the mirroring set up. Here are few points we started our analysis as below.

1. Verify DB Mirroring is working uninterrupted – if the mirroring is not working for some reason, on synchronous mode, the impact is huge, the principal server has to wait to commit.

2. Verify feasibility of DB Mirroring mode to Asynchronous – Changing Mirror mode from synchronous to Asynchronous is not a solution,however, its a workaround. Ideally, we need to identify if long running transaction/index maintenance etc caused the issue while applying the changes to Mirror.

3. Verify network latency between Principal and Mirror – The latency in the network is one of the reason that can be assessed using the perfmon counter.

Root cause and resolution

In our case, the root cause was the first one. For some reason, the mirroring has been broken, no endpoint was accepting the connection from the principal server. This lead to a situation on the principal server each time a transaction waits for its log (LSN) to be hardened on the mirror.Once we reestablished the mirroring, the wait type is removed and no blocking observed further.

How to get the status of Mirroring:


SELECT
DB_NAME(DATABASE_ID) AS [DATABASENAME],
CASE WHEN MIRRORING_GUID IS NOT NULL THEN 'MIRRORING IS ON' ELSE 'NO MIRROR CONFIGURED' END AS [ISMIRRORON],
[MIRRORING_STATE_DESC],
CASE WHEN MIRRORING_SAFETY_LEVEL=1 THEN 'HIGH PERFORMANCE' WHEN MIRRORING_SAFETY_LEVEL=2 THEN 'HIGH SAFETY' ELSE NULL END AS [MIRRORSAFETY],
MIRRORING_ROLE_DESC,MIRRORING_PARTNER_INSTANCE AS [MIRRORSERVER]
,MIRRORING_PARTNER_NAME AS [PARTNER NAME]
,MIRRORING_ROLE_DESC AS [MIRROR ROLE]  
,MIRRORING_SAFETY_LEVEL_DESC AS [SAFETY LEVEL]
,MIRRORING_WITNESS_NAME AS [WITNESS]
,MIRRORING_CONNECTION_TIMEOUT AS [TIMEOUT(SEC)]
FROM SYS.DATABASE_MIRRORING

See Also:
https://blogs.msdn.microsoft.com/grahamk/2011/01/10/sql-server-blocking-caused-by-database-mirroring-wait-type-dbmirror_dbm_event/

How do we recover unsaved query window in SQL Server Management Studio?

How do we recover the unsaved query window in SQL Server Management Studio?

This is a simple tip to recover your unsaved query window in SSMS.

As a SQL server professional, most of us will work closely with SSMS in a daily basis.At times, we forget to save our work in the management studio and for some reason if the query window closes/SSMS crashes, we will be in a position of “lost everything”. You may be questioned on your best practises. Here is a way to recover the SSMS window content which would save your time and effort.

You can verify the below path and get the files:

C:\Users\<>\Documents<>\SQL Server Management Studio\Backup Files

The above path may change depends on the windows version.

How does it happen?

There is a setting in Management Studio that allows the Autorecover option enabled by default. We can modify the default settings as per our need to reduce the risk of loss.

Go to SSMS -> Tools -> Options -> Environment -> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query window file at certain interval specified. You can set this value carefully on how frequent that you need to save your SSMS cahnges.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days.

Error Message – The index ” is dependent on column ”.ALTER TABLE ALTER COLUMN ” failed because one or more objects access this column.

It is always annoying when we get a request to change the datatype of an existing column for many reason. The changes are due to business reason or bad design or anything. But, when we change to accommodate, we need to do lots of analysis to ascertain the impact of the change.

Here, we are going to explain a case, changing datatype size varchar (n) to varchar(max). This blog ONLY explains the context of changing the datatype not the impact of the change in terms of performance.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX) -- This statement will fail

On executing the below snippet, we get the below error message. (Please note that an index is dependent on column)

Error Message:
Msg 5074, Level 16, State 1, Line 8
The index ‘IX_ColumnDatatypeChange’ is dependent on column ‘Col2’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

However, when we do the change from MAX to 8000, it gets executed without any issue.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(8000) -- This statement will succeed

Reason:

The reason for this behavior is due to the change from non-max type to a max type. SQL Server considers this change as dropping a column and adding a new column. Internally, the non-max and max types do not share the same storage types (Row-Overflow vs. LOB allocation units). Hence, this change needs to update every row and move the data from dropped column to newly added column. However, if you see for non-max type change, they share the same storage (either in-row or in the row-overflow allocation unit), so no issues with in-place update, that mean, no need to drop the dependent(index in this case).

Resolution:

1. Drop the index and change the column size followed by adding the index back to the table


Drop Index IX_ColumnDatatypeChange on ColumnDatatypeChange
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX)
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)

2. Analyze and try to avoid the size to MAX (if possible)

Thanks for your read and looking for your feedback!!!

New enhancement in SSMS in SQL Server

SSMS in SQL 2016 has come up with lots of new features. One of the features is enhanced scrollbar in SSMS. I personally believe the new enhancement in scrollbars will definitely help developers to work with SSMS with ease.

Let us look at the below picture to have an insight:

They are different color bocks on the right side of SSMS query window that shows the changes:
a. Green blocks show the saved lines of Query part in a window to disk
b. Yellow blocks show the query lines of unsaved/changed lines since last save to disk
c. Red blocks show any syntax errors in a query window
d. Blue block shows the current cursor location

It is definitely good to know to developers who plays around with SSMS very frequently.