SQL Server Performance Forum – Threads Archive
Restrict Access to SQL server via Certificates
I want to enable access to an instance of SQL Server 2005 for a sql server login only from certain servers. Based on what I’ve looked up I was under the impression I could do this by creating my own certificate. Setting ForceEncryption = Yes under Protocal Properties for my instance and under the Certificate tab setting the certificate to the one I had created.Basically the steps in this article –http://support.microsoft.com/kb/316898 My problem is that after setting up the certificate on the server I can still access that SQL instance without setting up the certificate on the client side. It’s as if I didn’t set up any certificates at all. Am I missing something? or am i misunderstanding what certificates can do for me. Thanks, Nam
Encryption does not solve access control problems. However, it enhances security by limiting data loss even in the normally rare occurrence that access controls are bypassed. Few points on the Certifications from BOL:
Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account. The client must be able to verify the ownership of the certificate used by the server. If the client has the public key certificate of the certification authority that signed the server certificate, no further configuration is necessary. If the server certificate was signed by a public or private certification authority for which the client does not have the public key certificate, you must install the public key certificate of the certification authority that signed the server certificate. Fyi,Certificates are software "keys" shared between two servers that allow secure communications by way of strong authentication and linkshttp://msdn2.microsoft.com/en-us/library/ms186362.aspx,http://msdn2.microsoft.com/en-us/library/ms187798.aspx too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks that clears up a lot.
So then is there a way to restrict access to sql server using a sql server login based on user location?
Yes, the access to the server can be controlled with specified login within SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I’m went to login properties for a sql login and didn’t see anything that fit what I was trying to do. So for example I have user testUser, and we have Clients A and B which can connect to the database instance. I want to enable testUsers to be able to connect from client machine A but not B. Thanks
Actually you totally answered my questions with your previous post. <img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ /> I just didn’t realize it at first. I just need to use a CA that’s not everyone has access to and then my original idea will work fine. <br /><br />Thanks satya<br /><br />Nam
]]>