SA: SQL Server Encryption from Access by Susan Pyne. Q&A with Ben Clothier - Access with SQL Server

In this excellent lesson for Microsoft Access to take advantage of SQL Server's great capabilities for data management on the back-end, Susan Pyne guides you through the steps to encrypt and decrypt a column of data that's stored in SQL Server in a table that Access can link to, and how to manipulate encrypted data. She demonstrates each basic step for encrypting and decrypting a column (field) using scripts written in Transact SQL (T-SQL), which run on SQL Server, and how to run those procedures from Access. She shows: • How to create keys and certificates in SQL Server. • Stored procedures to encrypt and decrypt the data. • Permissions in the SQL database. And then shows: • How to present the data in Access to allow users to view and / or edit the information using pass-through queries and VBA code that runs stored procedures. AUG: Encryption with SQL Server from Access Column encryption/decryption, T-SQL, Stored Procedures by Susan Pyne ( ITImpact.com ) brilliant answers by Ben Clothier ( ITImpact.com ) with Juan Soto ( ITImpact.com ) Tue 11 May 2021 Susan is the chapter coordinator for AccessUserGroups.org, a Senior Access Developer at IT Impact, and certified in Power BI. She loves working with Access and learning new technologies. Ben Clothier joins the session to answer questions. Juan Soto is a co-founder of AccessUserGroups.org and president of IT Impact. Download TEXT FILE with SQL Server scripts to Create the SQL Server Service Master Key, create a Self Signed SQL Server Certificate, create SQL Server Symmetric Key, encrypt a column, add/remove column, grant permissions, user defined function to decrypt data, and add/update/view records here, from accessusergroups.org: http://accessusergroups.org/sql-serve... CLICK ON A TIME or link to JUMP 4:52 What is Encryption? 6:03 Encryption types in SQL Server 6:07 Asymmetric, Symmetric 7:03 3rd-Party Certificates have an expiry date 7:17 Self-signed Certificate will continue to work 7:30 Transparent data encryption 7:43 Transact-SQL functions to encrypt and decrypt data 8:12 Key based encryption in SQL Server 8:15 service master key (SMK) generated by SQL Server 8:28 level before that -- Windows has a key that works with encryption 8:55 database master key (DMK), symmetric -- what is demonstrated, along with the certificate 9:16 outline 9:41 Encryption Hierarchy for Windows 10:44 Reference links 13:03 SQL Server stored procedures script 13:13 get service master key from SS 13:52 create master key encryption with password in database where data will be encrypted 14:29 create Self Signed SQL Server Certificate 15:18 self signed certificate can define Expiry date even though it still continues to function after that date 16:43 symmetric key 17:16 database security section 17:48 create column to store encrypted data for SSN - varbinary 18:59 Refresh Local Cache, encrypt the new column 20:31 read the data and decrypt 21:29 drop original column that wasn't encrypted and look at data 22:54 insert a record with encrypted column, and look at data 24:32 grant access to run stored procedures and functions 25:29 procedures to decrypt and view, last 4 digits 26:46 decrypt full value and view 27:24 update information 28:05 switch to Access 28:10 Form in Access shows only last 4 digits of SSN 28:30 design view of Access form 30:26 troubleshoot error updating encrypted information 30:46 VBA code to save 31:40 stored procedure to select and decrypt data 32:03 fix stored procedure to update encrypted data 32:27 Try to save again in Access, and it works. 32:50 pass-through queries 34:05 scripts 34:30 function to decrypt value 34:34 View that runs decryption function can be used as RecordSource for Access form 36:02 Questions & Answers with Susan Pyne and Ben Clothier How to grant permissions to just some users? Suggestion to use unbound forms. How much extra burden to encrypt a column that's central to the application? Decryption always done in back-end on SQL Server, never in the front-end. Security suggestion from Klaus to use stored procedures and views instead of linked tables, and show different views to different users or groups. With a view, you're decrypting everything instead of just a selection, so slower and less secure. AccessUserGroups.org Access with SQL Server hosted by Juan Soto 2nd Tuesday @ 6:30 pm Central time CT -- join us! ~~~~~~ SUBSCRIBE for MORE videos! ► https://bit.ly/AUGYoutube REGISTER here ► https://bit.ly/AUGSQL to our virtual chapter! ✓ Join our online Access User Groups with free membership. Monthly webinars on the latest topics: MS Access, MS Access with SQL Server, etc. ► Facebook ◄ https://www.facebook.com/AccessUserGr... ► Twitter ◄ https://twitter.com/AccessUserGroup https://twitter.com/AUGEspana https://twitter.com/MSAccessLatino