Restrict Access to SQL Server using "Certificates"
Within SQL Server 2005 you could take help of certificates to restrict the access from a particular client's machine. Few times (even me) confused with or get an impression that SSL encryption is same, for more information on SSL(Secure Sockets Layer) refer to KBA316898 in this case. There is much information available within SQL 2005 BOL in this case as compared to previous versions of SQL Server.
On the question, SQL Server 2005 has capability to use SSL to encrypt data transmitted across the network between the instance of SQL Server and/or client application. In this case Certificate is use to encrypt the data transmission that must be stored locally for the users on the computer. Say if the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.
Additionally Data 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.
On the 'simple terms' Certificates are software "keys" shared between two servers that allow secure communications by way of strong authentication. For your knowledge & convenience I would like to vist through the links on MSDN, Technet and web in this case :
MS186362
MS187798
Also the following links to learn more about SSL, Certificate Provisioning, and/or Data Encryption in SQL Server 2005.
http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
KBA276553
MS191192
MS189067
Further once you have a better understanding of encryption & accessibility you need to know more about the number of levels in the key hierarchy, as it will tend to get your time on database level certificates and symmettric keys, so refer to the SQL Server 2005 Encryption Hierarchy article for a detailed description of certificates, asymmetrical and symmetrical keys with an additional tour to the SQL Server 2005 Permissions Hierarchy.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.