secure connection to sql server for access db

Question

Hi,

 

Can anyone let me know how to have a secure connection to sql server from microsoft access.

 

Thanks

Answer 1

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

Answer 2

Thanks for the reply Joe.

Answer 3

HI Joe and ISRS

I was wondering if this solution is viable for SQL Server that is in a shared Hosting environment?

Regards 

Meir R.

---

meir r.