Thursday, April 21, 2011 5:46 AM
Can anyone let me know how to have a secure connection to sql server from microsoft access.
Thursday, April 21, 2011 1:32 PM
I take it you mean encrypted connections?
Don't know if Access presents problems with encrypted connections but you can enable "force encryption" for your SQL Server instance from the configuration manager. BOL documents this in detail so check it out for exact steps. Don't forget to get a proper certificate from your company's admins or a commercial CA.
Once you have "force encryption" enabled, restart the instance and connect from your Access app. If it cannot support encrypted connections, SQL Server should disallow the connection. If you can connect, run the script below to verify your connection is encrypted. Should be obvious from the output.
SELECT b.session_id, a.original_login_name, a.program_name, b.net_transport, b.protocol_type, b.encrypt_option FROM sys.dm_exec_sessions a, sys.dm_exec_connections b WHERE a.session_id = b.session_id
No great genius has ever existed without some touch of madness. - Aristotle
- Marked As Answer by WeiLin QiaoModerator Wednesday, April 27, 2011 10:25 AM
Thursday, April 21, 2011 5:46 PMThanks for the reply Joe.
Thursday, May 10, 2012 8:15 AM
HI Joe and ISRS
I was wondering if this solution is viable for SQL Server that is in a shared Hosting environment?