VBA/VBS Insert Into Always Encrypted Column
-
Hello~
I work on an application (Azure SQL Server Back End/MS Access Front End), and have been tasked with encrypting a few columns in our back end and ensuring our application continues to function properly.
Using the articles I've found here, I've been able to use SSMS's GUI Tool to encrypt the columns in question, create the certificate, and figured out the proper connection string so that the data is decrypted on the front end for the end users. So far so good.
Now I'm trying to figure out how to insert into a record into our table with encrypted fields and am having a hell of time. From what I have gathered so far, the way to do this is to create a stored procedure with parameters for the encrypted fields, and call said stored procedure to perform an insert. So as an example on a dummy table, I created the following very simple stored procedure:
CREATE PROCEDURE dbo.employee_add
@empfn NVARCHAR(50),
@empln NVARCHAR(50),
@empssn NVARCHAR(10)
AS
BEGIN
INSERT INTO dbo.employee
(employee_fname,
employee_lname,
employee_ssn)
VALUES
(@empfn,
@empln,
@empssn)
END'employee_ssn' is the encryted field. From what I gathered from the articles posted on this site, this procedure will not work calling it from SSMS, but must be called from a supported connection (ADO.NET 4.6, JDBC 6.0 and ODBC 13.1). So from VBS, I've written this:
'Connect to SQL
Set objConn = CreateObject("ADODB.Connection")
strConn = strConn & "DRIVER=ODBC Driver 17 for SQL Server"
strConn = strConn & ";SERVER=MYSERVER"
strConn = strConn & ";DATABASE=MYDB"
strConn = strConn & ";TrustServerCertificate=Yes"
strConn = strConn & ";Trusted_Connection=Yes"
strConn = strConn & ";ColumnEncryption=Enabled"
objConn.Open strConn
'Loop Through Raw Table to Populate Production Table
jsql = "SELECT employee_raw.* FROM employee_raw;"
Set jrs = objConn.Execute(jsql)
jrs.MoveFirst
Do until jrs.EOF
empssn = jrs.Fields("employee_ssn").Value
empfn = jrs.Fields("employee_fname").Value
empln = jrs.Fields("employee_lname").Value
Set spcom = CreateObject("ADODB.Command")
With spcom
.ActiveConnection = strConn
.commandtext = "employee_add"
.commandtype = 4
End With
Set sppar1 = spcom.CreateParameter("@empfn",200,1,50,empfn)
spcom.Parameters.Append sppar1
Set sppar2 = spcom.CreateParameter("@empln",200,1,50,empln)
spcom.Parameters.Append sppar2
Set sppar3 = spcom.CreateParameter("@empssn",200,1,10,empssn)
spcom.Parameters.Append sppar3
spcom.Execute
'** This doesn't work either
'empsproc = "EXEC employee_add '" & empfn & "','" & empln & "','" & empssn & "';"
'objConn.Execute(empsproc)
jrs.MoveNext
Loopj
rs.CloseWhether I try to create and append paramaters in VBS, or simply try to pass an 'EXEC [stored procedure]', I get the same error:
Perhaps this will not work with ODBC 17? Or is there something else entirely that I'm missing about this? Any help would be greatly appreciated. Thanks in advance!
-
From reading the error, it sounds like it is telling you exactly what is wrong - you are sending an NVARCHAR datatype and it requires a VARCHAR datatype.
Not 100% certain on the syntax in VBA/VBS but you will need to change your CreateParameter for empssn from NVARCHAR(10) to VARCHAR(10). Thinking it is either the 200 or the 1 that will need to be changed to a different value.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Hello Brian --
Thank You -- that was in fact the issue --
Scouring the microsoft articles, I came across this:
The SQL type of the parameter inserted into the SSN column is set to SQL_CHAR, which maps to the char SQL Server data type (rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);). If the type of the parameter was set to SQL_WCHAR, which maps to nchar, the query would fail, as Always Encrypted does not support server-side conversions from encrypted nchar values to encrypted char values.
I surmised from this that when creating a parameter for the encrypted field, the data type must be 'SQL_CHAR'. There did not seem to be any equivalent in VBA/VBS. But simply going through all the data types and trying many of them, it seems that 'adVarWChar' is the equivalent -- it's working for me at least.
So in my example, the stored procedure defines the encrypted SSN field as NVARCHAR(10), but in the Parameter Definition, I cannot use 'adVarChar' which I had been using for the other non-encrypted fields. '200' is the code for 'adVarChar', and '202' is the code for 'adVarWChar'. Changing the datatype to 202 in the encrypted field seems to solve the issue:
Set sppar1 = spcom.CreateParameter("@empfn",200,1,50,empfn)
spcom.Parameters.Append sppar1
Set sppar2 = spcom.CreateParameter("@empln",200,1,50,empln)
spcom.Parameters.Append sppar2
Set sppar3 = spcom.CreateParameter("@empssn",202,1,10,empssn)
spcom.Parameters.Append sppar3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply