RE: Help Newbie..Upload file from SQL Server

From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 05/06/04 

Date: Thu, 06 May 2004 09:57:16 GMT

vishalsu@online.microsoft.com

ISSUE:
=======
 Is it possible to install certificates to do a SSL on SQL server 2000.

RESOLUTION /LINKS:
====================

HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
============================================================================
==
View products that this article applies to.
This article was previously published under Q316898
IN THIS TASK
SUMMARY

Steps to Use to Install a Certificate on a Server with Microsoft Management
Console (MMC)
Steps to Enable Encryption for a Specific Client
How to Test Your Client Connection
REFERENCES
SUMMARY
This step-by-step article demonstrates how to install a certificate on a
that is running SQL Server 2000 by using Microsoft Management Console (MMC)
and describes how to enable SSL Encryption at the server, or for specific
clients.

NOTE: You cannot use this method to put a certificate on a SQL Server
clustered server.

If your company has implemented an Enterprise Certificate Authority, you
can request certificates for a SQL Server 2000 stand-alone server, and then
use the certificate for Secure Sockets Layer (SSL) encryption.

You can enable the Force Protocol Encryption option on the server, or on
the client.

NOTE: Do not enable the Force Protocol Encryption option on both the client
and the server. To enable Force Protocol Encryption on the server, use the
Server Network Utility. To enable Force Protocol Encryption on the client,
use the Client Network Utility.

IMPORTANT: If you enable SSL encryption by using the Client Network
Utility, then all connections from that client will request SSL encryption
to any SQL Server to which that client connects.

WARNING: If you enable Force Protocol Encryption on the client computer,
you cannot connect to previous versions of SQL Server from that specific
client. Previous versions of SQL Server do not recognize SSL encryption.

If you enable Force Protocol Encryption on the server, you must install a
certificate on the server.

If you want to enable Force Protocol Encryption on the client, you must
have a certificate on the server and the client must have the Trusted Root
Authority updated to trust the server certificate.

back to the top
Steps to Use to Install a Certificate on a Server with Microsoft Management
Console (MMC)
To use SSL encryption, you must install a certificate on the server. Follow
these steps to install the certificate by using the Microsoft Management
Console (MMC) snap-in.

How to Configure the MMC Snap-in
To open the Certificates snap-in, follow these steps:

To open the MMC console, click Start, and then click Run. In the Run dialog
box type:

MMC
On the Console menu, click Add/Remove Snap-in....
Click Add, and then click Certificates. Click Add again.
You are prompted to open the snap-in for the current user account, the
service account, or for the computer account. Select the Computer Account.
Select Local computer, and then click Finish.
Click Close in the Add Standalone Snap-in dialog box.
Click OK in the Add/Remove Snap-in dialog box. Your installed certificates
are located in the Certificates folder in the Personal container.
Use the MMC snap-in to install the certificate on the server:

Click to select the Personal folder in the left-hand pane.
Right-click in the right-hand pane, point to All Tasks, and then click
Request New Certificate....
The Certificate Request Wizard dialog box opens. Click Next. Select
Certificate type is "computer".
In the Friendly Name text box you can type a friendly name for the
certificate or leave the text box blank, and then complete the wizard.
After the wizard finishes, you will see the certificate in the folder with
the fully qualified computer domain name.

If you want to enable encryption for a specific client or clients, skip
this step and proceed to the Steps To Enable Encryption for a Specific
Client section of this article.

If you want to enable encryption at the server, open the Server Network
Utility on the server where the certificate is installed, and then click to
select the Force protocol encryption check box. Restart the MSSQLServer
(SQL Server) service for the encryption to take effect. Your server is now
ready to use SSL encryption.
back to the top
Steps to Enable Encryption for a Specific Client
For the client to request the SSL encryption, the client computer must
trust the server certificate and the certificate must already exist on the
server. You have to use the MMC snap-in to export the Trusted Root
Certification Authority used by the server certificate:

To export the server certificate's Trusted Root Certificate Authority (CA),
follow these steps:

Open MMC, and then locate your certificate in the Personal folder.
Right-click the certificate name, and then click Open.
Review the Certification Path tab. Note the top most item.
Navigate to the Trusted Root Certification Authorities folder, and then
locate the Certificate Authority noted in step c..
Right-click CA, point to All Tasks, and then click Export.
Select all the defaults, and then save the exported file to your disk where
the client computer can access the file.
Follow these steps to import the certificate on the client computer:

Navigate to the client computer by using the MMC snap-in, and then browse
to the Trusted Root Certification Authorities folder.
Right-click the Trusted Root Certification Authorities folder, point to All
Tasks, and then click Import.
Browse, and then select the certificate (.cer file) that you generated in
step 1. Select the defaults to complete the remaining part of the wizard.
Use the SQL Server Client Network Utility.
Click to select the Force Protocol encryption option. Your client is now
ready to use SSL encryption.
back to the top
How to Test Your Client Connection
To test your client connection you can either:
Use the Query Analyzer Tool.

-or-

Use any ODBC application where you can change the connection string.
Query Analyzer Tool

To use the Query Analyzer Tool, follow these steps:
Use the SQL Server Client Network Utility.
Click to select the Force protocol encryption option.
Connect to the server that is running SQL Server 2000 by using Query
Analyzer.
Monitor the communication by using Microsoft Network Monitor or a Network
Sniffer.
ODBC or OLEDB Application Sample Connection Strings

If you use ODBC or OLEDB connection strings, follow these steps:

Modify the ODBC or OLEDB connection string. For example:

ODBC

Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Netwo
rk=DBNETLIB.DLL;Encrypt=YES

OLEDB

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use
Encryption for Data=True
Connect to the server that is running SQL Server 2000, and then monitor the
communication by using Microsoft Network Monitor or a Network Sniffer.

LINKS:
======
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316898
http://support.microsoft.com/default.aspx?scid=kb;en-us;276553&sd=tech
This posting is provided "AS IS" with no warranties, and confers no rights.

Relevant Pages

  • Re: Socket Server with Encryption help
    ... Before the client ... Authentication protocols are fiercely difficult to get right. ... by Needham and Schroeder "Using encryption for authentication in large ... Client connects into Server and Server accepts the connection. ...
    (microsoft.public.dotnet.security)
  • Re: Need for encryption in WSE 3.0 if using SS-avoid man-in-middle
    ... SSL only validates you are talking to a SSL certified server; ... They can simply edit the URL the client program ... can be done by using a X.509 certificate on both ends, ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: LDP client authentication fails
    ... I got the LDP working with LDAP server under server client authentication ... I did not installed the certificate in pfx format .. ... Client cert auth won't work without that. ...
    (microsoft.public.windows.server.active_directory)
  • Re: SSL & Man In the Middle Attack
    ... >> it possible for the middle man to intercept all messages from server to me ... > server sends client a signed message along with a digital certificate. ... > client generates a random secret key, ...
    (comp.security.misc)
  • Re: activesync issue
    ... On the SBS 2003 Server open the Server Management console. ... On the "Web Server Certificate" page, choose to create a new Web server ... Install the new certificate which created in above step on mobile device: ... Access to browse the Exchange Server 2003 client after you install ...
    (microsoft.public.windows.server.sbs)