This is part 3 in the SQL Always Encrypted mini series...
Unfortunately, the gotchas continue, as it isn't straight forward when using yet more toolsets to access Always Encrypted data. All easily doable, as long as you make a tweak or two to the normal methods you may be using already.
Part 1 (here) we covered the basics of Always Encrypted with SSMS, and some of its gotchas.
Part 2 (here), we handled how to read the Encrypted data using Power BI / PowerBI Desktop, which also contained some 'alternative configurations' that you needed to make use of as the standard methods were not enough.
So now, lets see how it plays with another one of those common toolsets that you may use alongside your Encrypted data. In this post, i'll be talking about accessing and importing data using SSIS, nothing fancy, just reading data from an Excel sheet, and piping into our Always Encrypted table, encrypting as we go.
I'm not saying to use Excel for housing confidential data either!... as no one does that...oh no, not anywhere, ever....</sarcasm>.
As previously, this focuses on using Azure Key Vault for securing Encryption keys required.
Like before, our encrypted table definition is as below, keeping things simple.
USE AETesting;
CREATE TABLE dbo.EncryptedTable(
Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
LastName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
FirstName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);And our SSIS package looks like this with a single dataflow from Excel to SQL.
So, what happens when we use a standard SQL Server Destination with Native OLE DB connection for SQL as shown?
OLE DB Destination.
Previewing Results
oh...oh.
Lets have a look at the column properties in the Advanced Editor on our Destination, now thats odd.... it thinks its a byte stream.
This actually makes sense as it is reading encrypted data, so to SSIS it just looks like a byte stream.
And if you tried to run this package, you may recieve an error such as:
Error: 0xC0202009 at Load OLE, OLE DB Destination [21]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
Ah, so its another case of the OLE SQL driver not supporting the Always Encrypted data. Lovely jubbly.
ODBC Destination
Aha, you've already read the post for Power BI and Always Encrypted data (here), where we worked around the lack of support by using ODBC, so lets try switching the SQL OLE connection manager out for ODBC instead.
This is pointed at the DSN we created at that particular blog post, so we know it works, and it has the correct key values for accessing our Azure Key Vault in there ok. Here, i've just built the connection string from that System DSN we created. I'd prefer to have the connection string in there and parameterized, to avoid the need for DSN creation wherever its deployed, but this is up to you.
So in our ODBC Destination, if we View Existing Data.
Great, we can see Unencrypted data, which means our Key is being read correctly. So lets run our load now to write more data.
Dammit, Janet!
Error: 0xC0014020 at Load OLE, ODBC Destination [21]: SQLSTATE: 22018, Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Operand type clash: nvarchar(102) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting') is incompatible with nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting');
That looks like the error we recieved when trying to insert data non parameterized in SSMS....(Part 1) Spolier Alert: It is that...it has nothing to do with data types. I've matched up my datatypes in the package completely.
ADO .NET Destination
Lets change that to an ADO.NET destination instead, and then select the ODBC Data Provider. Like so...
Select the ODBC DSN as before and use it, build the connection as needed.
Our flow now looks like this so lets run it.
Boom, the light is green, the trap is clean.
If we look in our SQL Table now, the data has been imported. Hurrah!
Summary
OK, so the takeaway for this one is when you are using SSIS against Always Encrypted Columns is that you need to use an ADO NET Destination, and then use the ODBC Data Provider for this.
SSIS and Always Encrypted columns? Use ADO.
Although you can read the data in Preview using a native ODBC connection, you cannot write it as the parameterization is not correct. Care is needed with diagnosing the error messages received too, as although it looks like a datatype mismatch, and the connection looks like it's working...it really isn't
I also suspect that using ODBC and reading the data with a predicate will also fail with a similar error...just haven't tested that.
Thanks for reading, and hopefully this helps someone, somewhere avoids a headache.
Rodnado
Comments