Installing Certificate on SQL Server 2005

Hi -

I'm trying to implement encryption using a 3rd party certificate. I'm following Uncle Bill's documentation on how to install a cert on the server using MMC... I can import the cert into the certificate store just fine. However, when I go to the SQL Server Configuration Manager and bring up the properties window for "Protocols for <Servername>" there are no certs in the dropdown list on the "Certificate" tab.

My certificate is valid... has a good date, etc. What could I be doing wrong? I thought I might have it in the wrong spot in the certificate store, but have moved it all around with no change in result...

Any help would be greatly appreciated!

Chris

Hi, Chris

SQL Server 2005 only displays certificates that are valid for usage with SQL 2005 encryption. So it verifies the following things:

1. Enhanced Key Usage section of cert contains:

Server Authentication (1.3.6.1.5.5.7.3.1)

2. Certificate has private key, and mark private key as exportable when generating it.

3. Certificate is in correct store (should be in Local Computer store under Personal Certificates for SQL running under localsystem or network service). .

4. Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM for you to choose the cert.

5. SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert,check your server errorlog, you could find key word "self-signed". In another word, you always can make encryption connection if you force server encryption, but, if you force client encryption, you will get error"certificate was not trusted" unless you specify trusting server cert through configuration,search more information in Books online.

BTW, you can refer following blog to gain more info about certificate in SQL Server 2005:

http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

HTH

Ming.

Installer for an SQL database?

Hi I have a .NET web application that embedd crystal reports and some third
party dlls and this interfaces with a Microsoft SQL 2000 dbase. The dbase
contains several tables with constraints and key relationships as well as
many stored procedures. Just wondering if there is a way to use Installsheild
to automatically install the database on a new server for deployment. I have
used the built in .NET web deploy wizard that creates an MSI file (windows
starter file) that can be run from the server.
Thanks.
--
Paul G
Software engineer.Paul wrote:
> Hi I have a .NET web application that embedd crystal reports and some
> third party dlls and this interfaces with a Microsoft SQL 2000 dbase.
> The dbase contains several tables with constraints and key
> relationships as well as many stored procedures. Just wondering if
> there is a way to use Installsheild to automatically install the
> database on a new server for deployment. I have used the built in
> .NET web deploy wizard that creates an MSI file (windows starter
> file) that can be run from the server.
> Thanks.
Do you need to create the database as well? If so, I would build this
into an application and make sure to provide all the details for
database creation that are available for SQL Server. Or you can give
users the standard specs for the database and have them create it
manually. In either case, if the database is available from your
installer, you can launch your utility to allow connection to the
database and then send all the DDL scripts to the database to create the
objects and possibly data. You could also use OSQL from the command-line
to send SQL statements. They key here is whether you need to create the
database.
--
David Gugick
Imceda Software
www.imceda.com