By: Mircea Dragan | Updated: 2020-03-20 | Comments | Related: > Microsoft Access Integration
Problem
Microsoft Access is still used in many places, but sometimes it is necessary to port the data to SQL Server instead. One reason is that the database can be a lot larger in SQL Server compared to Access. Another reason is there is much better security in SQL Server (in Access security is nonexistent for example in old mdb Access files). When you decide to migrate from Access to SQL Server, it is better to use the latest security features that SQL Server offers.
Solution
Starting with SQL Server 2017 Microsoft introduced an extra layer of security, Always Encrypted columns. I will not enter into details what it means and I assume you are already familiar with the concept. From what I have seen, everybody presents how to create a database with Always Encrypted columns by either using SSMS or PowerShell. That means if you want to create such a database, your presence at the computer is required.
Importing an Access database into SQL Server can be also easily done using SSMS.
Let assume you have customers who use Access database and you give them a new version of the program which uses SQL Server instead. In this case you have to import Access database into SQL Server. What happens if you don’t have physical access to SQL Server (not even remotely)? The ideal case is to offer your customer a program which creates the always encrypted database with the same structure as the Access database, and imports the data from Access into the newly created database.
If you don’t know already how to do this, keep reading and you will find out. The solution I present does not use PowerShell or SSMS. It uses only Transact SQL statements and the necessary C# statements to work with certificates. The solution assumes you already have the certificate to use for encryption. If you don’t have a certificate, you can create a self-signed one. Creating the certificate is not part of the solution, but I include it as an example in case you want to do it yourself.
I used for this presentation SQL Server 2019 and Visual Studio 2019. Some of the C# code will not work if you don’t use the latest .NET (currently 4.8)
Getting the Access Database Schema
In order to import an Access database, you need its schema. Using the schema, you can easily create the corresponding SQL Server database. A good start on how to create the schema for an Access database is given in the references of this article. I assume you read that article, because it is necessary to understand the type of each field in tables.
I created a simple Access database and the schema for one table is:
<Database>
<Tables>
<Table>
<Name>Company</Name>
<Fields>
<Field>
<Name>CompanyCode</Name>
<ValidationRule/>
<ValidationText/>
<Default/>
<Size>8</Size>
<Required>0</Required>
<Type>10</Type>
<Attributes>2</Attributes>
<AllowZero>1</AllowZero>
</Field>
<Field>
<Name>CompanyName</Name>
<ValidationRule/>
<ValidationText/>
<Default/>
<Size>24</Size>
<Required>0</Required>
<Type>10</Type>
<Attributes>2</Attributes>
<AllowZero>1</AllowZero>
</Field>
<Field>
<Name>BIC</Name>
<ValidationRule/>
<ValidationText/>
<Default/>
<Size>11</Size>
<Required>0</Required>
<Type>10</Type>
<Attributes>2</Attributes>
<AllowZero>1</AllowZero>
</Field>
<Field>
<Name>IBAN</Name>
<ValidationRule/>
<ValidationText/>
<Default/>
<Size>34</Size>
<Required>0</Required>
<Type>10</Type>
<Attributes>2</Attributes>
<AllowZero>1</AllowZero>
</Field>
</Fields>
<Indexes>
<Index>
<Name>CompanyCode</Name>
<Primary>1</Primary>
<Required>1</Required>
<IgnoreNulls>0</IgnoreNulls>
<Unique>1</Unique>
<Fields>1</Fields>
<IndexedFields>
<Field>
<Name>CompanyCode</Name>
<OrderBy>0</OrderBy>
</Field>
</IndexedFields>
</Index>
</Indexes>
</Table>
</Tables>
</Database>
In this table the IBAN field contains confidential data, so I want to create an always encrypted column for it. Because Access doesn’t have a possibility to say which fields should be created in SQL Server as always encrypted, I created a second xml file based on the file with the schema, which looks like:
<Database>
<EncryptedTables>
<Table>
<Name>Company</Name>
<Fields>
<Field>
<Name>IBAN</Name>
</Field>
</Fields>
</Table>
</EncryptedTables>
</Database>
Delete Previous Database
If you want to have a brand-new SQL database to import into, you need to remove the previous one, in case it exists. In order to do this, you need first to close all connections to the old database. You can do this with the following code:
_connStr = "Data Source=" + _dbServer + ";Initial Catalog=" + _dbName +
";Integrated Security=SSPI;Connect Timeout=30;Column Encryption Setting=enabled;";
//
// Other lines of code, see the attached solution for full source
//
string connectToMaster = "Data Source=" + _dbServer +
";Initial Catalog=master;Integrated Security=SSPI;Connect Timeout=90";
using(SqlConnection conn = new SqlConnection(connectToMaster))
{
conn.Open();
using SqlCommand cmd = new SqlCommand
{
Connection = conn
};
string query;
//
// Other lines of code, see the attached solution for full source
//
query = "IF DB_ID ('" + _dbName + "') IS NOT NULL\n" +
"BEGIN\n" +
" DECLARE @kill varchar(8000);\n" +
" use master;\n" +
" SELECT @kill = 'kill ' + CONVERT(varchar(5), req_spid) + ';'\n" +
" FROM master.dbo.syslockinfo\n" +
" where rsc_type = 2 AND rsc_dbid = db_id('paytime-test');\n" +
" exec(@kill);\n" +
" alter database [" + _dbName + "] set single_user with rollback immediate;\n" +
" drop database [" + _dbName + "];\n" +
"END;\n" +
"CREATE DATABASE [" + _dbName + "]\n" +
"ON (NAME=[" + _dbName + "], FILENAME='" + dbName + ".mdf');\n";
conn.Close();
cmd.CommandText = query;
}
As a note, when you want to connect to an always encrypted database, you have to specify in the connection string the Column Encryption Setting=enabled option. If you want to use SSMS, you also have to specify this option.
If You Don’t Have a Certificate, Create a Self-Signed One
If we want to use an always encrypted database, we need a certificate for encryption. This certificate is what you will have to give to everybody who wants to access the encrypted data. I will demonstrate how to use the certificate by installing in the current user’s certificate store. This way every user who logs into that computer which accesses the always encrypted database needs to have the certificate loaded into their local certificate store. I will not discuss if you can have one certificate per computer, you can do a research how you can do it.
If you don’t have already a certificate, you can create a self-signed one, using for example the command in PowerShell:
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedTest"
-FriendlyName "Always Encrypted SQL Test" -CertStoreLocation Cert:CurrentUser\My
-KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment
-KeySpec KeyExchange -KeyLength 2048 -Provider "Microsoft Strong Cryptographic Provider"
-HashAlgorithm SHA256
Once you create the self-signed certificate, export it to a pfx file. You have to export the private key as well, using Certificate Manager. I exported this certificate in a file I called TestAlwaysEncrypted.pfx, with the password Sin[Pi/2]=1. I provided the password I used for this certificate, because I will attach the certificate, Access database, schemas for both database and always encrypted columns and the C# solution to this article, so you can try it yourself.
Install the Certificate in Your Local Certificate Store
With the certificate I provide all you need to do is to install it in your local certificate store. The same way, when you give your customer your certificate, they will also need to install it. You don’t need to use Certificate Manager or any other program. We can do it from C#, using the code below.
// Get certificate and its thumbprint
X509Store store = new X509Store(StoreName.My, StoreLocation.CurrentUser);
store.Open(OpenFlags.MaxAllowed);
string thumbprint = string.Empty;
foreach(X509Certificate2 cert in store.Certificates)
{
// Search for certificate and get the thumbprint
if(cert.Subject.Substring(3).ToLower() == "alwaysencryptedtest")
{
thumbprint = cert.Thumbprint;
store.Close();
break;
}
}
if(string.IsNullOrWhiteSpace(thumbprint)) // the certificate was not found, load it
{
if(string.IsNullOrWhiteSpace(_certFile) || !File.Exists(_certFile))
{
_error += "\r\nError: Missing certificate file";
Console.WriteLine("\nError: Missing certificate file");
_haveErrors = true;
}
if(string.IsNullOrWhiteSpace(_certPass))
{
_error += "\r\nError: Missing certificate password";
Console.WriteLine("\nError: Missing certificate password");
_haveErrors = true;
}
try
{
X509Certificate2 x509 = new X509Certificate2(_certFile, _certPass,
X509KeyStorageFlags.PersistKeySet | X509KeyStorageFlags.UserKeySet |
X509KeyStorageFlags.Exportable);
thumbprint = x509.Thumbprint;
store.Add(x509);
store.Close();
}
catch(Exception err) // Catch all errors, since we have an invalid certificate
{
_error += "\r\nError loading cert file: " + err.Message;
Console.WriteLine("Error loading cert file: " + err.Message);
_haveErrors = true;
}
}
The above code searches for certificate to see if it is not already in the store. If not, then it installs it.
Create Column Master Key
First step in implementing always encrypted is to create column master key. This can be done with the following code:
string certPath = "CurrentUser/My/" + thumbprint;
string query = "if not exists(select name from sys.column_master_keys where name = '" +
_cmk.Replace("[", "").Replace("]", "") + "')\n" +
"CREATE COLUMN MASTER KEY " + _cmk + "\n" +
"WITH\n" +
"(\n" +
" KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',\n" +
" KEY_PATH = '" + certPath +
"'\n)";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
Create Column Encryption Key
Second step in implementing always encrypted is to create column encryption key. I haven’t seen this step performed by using either PowerShell, or SSMS. The reason for this in my opinion is that people wondered where they will get the ENCRYPTED_VALUE parameter (which is a very long binary value) which is necessary in statement CREATE COLUMN ENCRYPTION KEY.
After several attempts I realized that I can get the long binary value starting from thumbprint of my certificate which I will use for encryption, compute the hash value using SHA256CryptoServiceProvider, create a new SqlColumnEncryptionCertificateStoreProvider and use it to encrypt the hash value by using the RSA_OAEP algorithm (the same algorithm used in CREATE COLUMN ENCRYPTION KEY statement). Bingo!
The code sequence which does this is:
var certProvider = new SqlColumnEncryptionCertificateStoreProvider();
byte[] thumb = Encoding.UTF8.GetBytes(thumbprint);
using SHA256CryptoServiceProvider sha = new SHA256CryptoServiceProvider();
byte[] thumbhash = sha.ComputeHash(thumb);
byte[] encryptedValue = certProvider.EncryptColumnEncryptionKey(certPath, "RSA_OAEP", thumbhash);
string encrypt = BitConverter.ToString(encryptedValue).Replace("-", "");
query = @"if not exists(select name from sys.column_encryption_keys where name = '" +
_cek.Replace("[", "").Replace("]", "") + "')\n" +
"CREATE COLUMN ENCRYPTION KEY " + _cek +
" WITH VALUES (COLUMN_MASTER_KEY = " + _cmk +
", ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x" + encrypt + ")";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
Importing tables from Access
Loading table schema from the XML file is easy:
XElement xmlDoc = XElement.Load(_dbstructFile);
_tables = xmlDoc.Elements("Tables").Descendants("Table");
We need to open a second connection to the Access database. The connection string looks like:
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
_accessFile + ";User ID=Admin;";
Importing tables is done in the code:
using OleDbConnection dbconn = new OleDbConnection(connectionString);
dbconn.Open();
OleDbCommand dbcmd = new OleDbCommand
{
Connection = dbconn
};
foreach(XElement table in _tables)
{
string tableName = "[" + table.Element("Name").Value + "]";
dbcmd.CommandText = "select * from " + tableName;
ImportTable(conn, dbcmd, tableName);
_dbProgress.PerformStep();
}
dbconn.Close();
The import is performed by the ImportTable() function:
private void ImportTable(SqlConnection conn, OleDbCommand dbcmd, string tableName)
{
Console.Write("Import table {0}...", tableName);
SqlTransaction transaction = conn.BeginTransaction();
try
{
using(DataTable dt = new DataTable())
{
using(OleDbDataAdapter adapter = new OleDbDataAdapter(dbcmd))
{
dbcmd.CommandType = CommandType.Text;
adapter.SelectCommand.CommandTimeout = 240;
adapter.Fill(dt);
}
using SqlBulkCopy copy = new SqlBulkCopy(conn,
SqlBulkCopyOptions.AllowEncryptedValueModifications, transaction);
copy.DestinationTableName = _schema + "." + tableName;
copy.BatchSize = 1000;
copy.BulkCopyTimeout = 240;
copy.WriteToServer(dt);
copy.NotifyAfter = 1000;
}
transaction.Commit();
Console.WriteLine(" done");
}
catch(Exception e)
{
string err = _importTableMsg + tableName + ": " + e.Message +
(e.InnerException == null ? "" : " (" + e.InnerException.Message + ")");
_error += "\r\n" + err;
MessageBox.Show(err, "Import Table Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
try
{
transaction.Rollback();
}
catch(Exception ex)
{
string err1 = "Rollback import table exception: " + ex.Message;
_error += "\r\n" + err1;
MessageBox.Show(err1, "Rollback Import Table Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
As you can see, we have to specify the AllowEncryptedValueModifications option in SqlBulkCopy, otherwise we get an error trying to copy a table with always encrypted column.
Next Steps
- I attached the full C# solution, together with the Access database, both schemas (included encrypted columns), the certificate which I talked about at the beginning of this article, and the Json configuration file used by C# program. The certificate password is also specified in the article.
- You can create your own certificate if you don’t want to use the one I provide.
- The program uses command line parameters:
-f <configfile>
-p <password>
These parameters can be specified in any order. If you use the solution, they are already there, but you may need to change the path to the config file. - You may need to install Access runtime. The solution uses by default x64 configuration. I attach a reference where you can find how you can have both Access runtime x86 and x64 running on the same computer.
References
- https://techblog.aimms.com/2014/10/27/installing-32-bit-and-64-bit-microsoft-access-drivers-next-to-each-other/
- https://stackoverflow.com/questions/698839/how-to-extract-the-schema-of-an-access-mdb-database
About the author
Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-03-20
