Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

SQL Server cannot find Certificate

Tags:

sql-server

ssl

Background Information:

I have a single Window VPS at example.com. I have it running IIS and SQL Server. I have a certificate for example.com that works fine with IIS. I want to use the same certificate for SQL Server to allow encrypted connections with clients. SQL Server Configuration Manager does not present the certificate in the drop down.

I believe the problem is that SQL Server does not think the certificate is valid, because what SQL Server thinks the server name is does not match the certificate (example.com).

How do I check what SQL Server thinks the server name is? If it is wrong how would I change it?

like image 765
Tobias Avatar asked Feb 18 '12 16:02

Tobias

People also ask

How do I find certificates in SQL Server?

In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties. On the Certificate tab, select the desired certificate from the Certificate drop-down menu, and then click OK.

How do you check SSL enabled or not in SQL Server?

To identify if SQL SERVER database is SSL enabled or not, run the following query: "SELECT session_id, encrypt_option FROM sys. dm_exec_connections". It should be run by Database Administrator.

Can not find server certificate with ThumbPrint?

To restore the database there is no need to specify any arguments about using the certificate, just use the standard restore command. When the backup is restored, SQL Server will look at the header of the backup file and this has the ThumbPrint of the certificate that was used to encrypt the backup.

How do I find server certificates?

Select Run from the Start menu, and then enter certmgr. msc. The Certificate Manager tool for the current user appears. To view your certificates, under Certificates - Current User in the left pane, expand the directory for the type of certificate you want to view.

1 Answers

Okay I found out the issue.

  1. The hostname on my machine was wrong. It wasn't "example.com", but some name randomly generated by windows. You can set this in the computer's properties window.

  2. The certificate was not registered to be used on port 1433. I had to use netsh to enable the certificate to be used on port 1433. Instructions here: http://msdn.microsoft.com/en-us/library/ms186362(v=SQL.100).aspx

Those two steps where complete I got the certificate to show up in SQL Server Configuration Manager, but I still had a problem went I attempt to run SQL Server. It would not start with a message from the logs saying it could not find or read the SSL Certificate.

  1. The last step was making sure the account running SQL Server had permission to read the certificate. I went into the certificate snap-in and then went to properties under the certificate, then on the Security tab I gave the Network Services account read permission on the certificate. (NOTE: I did not find any tutorials online reference this step. It was just some dumb luck that I found this.)

After those steps where complete the SQL Server Service start up with out any problem.

like image 197
Tobias Avatar answered Sep 30 '22 17:09

Tobias
Sign in to Comment

Related questions

primary key with additional data
Non primary key Identity AutoIncrement Mapping using Fluent NHibernate
ORDER/SORT Column mixed number and number prefixed by char
How should developers connect to SQL Server?
how to deploy .mdf file
SQL Server 2008 TempDB on other HD
SQL Server stored procedure conversion to SSIS Package
SQL Efficiency - Query using dateAdd Function twice; or SubQuery and DateAdd Function once; on Date BETWEEN
How is compressed data stored in the buffer cache, compressed or uncompressed?
How to order values inside group by
SQL Server split a single column multiple times
I need good tool for database unit testing
Average in order by clause
How does SQL Server know what precision to use for money
What does FixedLenNullInSource in sp_help mean?
Linq to SQL: Where clause comparing a Nullable<DateTime> with a SQL datetime null column
Generate CREATE TABLE script from SELECT statement
Parsing nested XML into SQL table
Sql query to find the all subordinates under a single superior
How to store DropDownList information in SQL

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!