If anyone finds any inaccuracies on this website please send me an email - address on the home page.

Capturing timeouts

The only way to capture timeouts is via SQL Trace - you need to know what was running/executing at the time of the timeout and SQL Trace is the tool to capture this. Do not confuse SQL Trace with SQL Profiler - SQL Trace is the collection of stored procedures that are used by profiler for capturing events. SQL Profiler is a Client Side Trace - SQL Trace is a Server Side Trace. Never, ever, run an extended duration trace using SQL Profiler. Most modern servers will handle it but for a busy server it can be enough to cause a blue screen and a server restart. Doing this is what I call "DBA's behaving badly!"

Setup a server-side trace by using SQL Profiler starting with a blank template and add the following items...

Errors and Warnings:

Attention - this is the actual timeout event. This only tells you a timeout has occurred. You need the following and associate this event with what was running when the timeout occurred.

Stored Procedures:






Normally we would use the completed objects but remember with a timeout they don't complete so we want the starting objects. If you want to filter the trace for a particular database then add this in now.

Click Run and then Stop the trace. Go Export > Script Trace Definition and save the script to a file. Using SSMS, open the script you just created and connect to the server concerned.

Edit the parameters for @maxfilesize and the sp_trace_create command. I would use the file rollover option to keep a predetermined number of files that you can overwrite on a daily basis without the files getting too large and also consuming all your disk space.

Once running, it is possible to query the live trace file by using one of the built-in SQL functions... fn_trace_gettable() You pass in the filename and then the number of files to read, or DEFAULT to read all files.


SELECT * FROM fn_trace_gettable('D:\MSSQL\Trace\MyTraceFile.trc', DEFAULT)

The EventID for a timeout is 16 - this can be found by querying sys.trace_events

After the tace has been running a while, run a query to see if there have been any timeouts. For example...

SELECT * FROM fn_trace_gettable('D:\MSSQL\Trace\MyTraceFile.trc', DEFAULT) WHERE EventClass = 16

if you get a result set back, note the times and correlate that with a particular file range so that the can query just that file and run something like the following...


FROM FN_TRACE_GETTABLE('E:\MSSQL\TimeoutTrace_30.trc', 1) AS a


WHERE a.EventClass = 16

AND b.StartTime > DATEADD(ss, -30, a.StartTime)

AND b.StartTime <= a.StartTime


What is happening here is we are performing a self join on one of the trace files. In the first result set is the Attention event (16) and joining on the SPID of that event to the same file getting all the events in the last 30 seconds on the same SPID. This should reveal what command was running when the timeout occurred. You can use this information to diagnose further what the problem is but at least we now what was running at the time :-)



Adding a location for your saved SQL scripts into SSMS

I was searching for this on the internet because the registry keys for the default save location can't be modified - well actually they can, but they just keep getting over-written. So, I found this which is a neat way of doing it...



Creating a Plan Guide for Red Gate SQL Backup Pro

One day while looking at cache usage on a particular server, I was surprised at the size of the procedure cache. Looking closely, there were a large number of single use plans in the cache. I looked at the SQL text to find out what these queries were and a large percentage was queries generated by Red Gate Software's SQL Backup Pro. We use SQL Backup exclusively in our environment so it is likely this will be occurring on all of our servers and a quick check confirmed this. At least this isn't a one-off. This looks like normal behaviour going by the text, so then I decided it would be a good idea to force a query plan through parameterisation. These are the steps I went through to get to this point…

How big is the procedure cache?

SELECT  SUM(single_pages_kb + multi_pages_kb)
FROM sys.dm_os_memory_cache_counters

Compare the size of this to your overall memory. By default all our SQL Servers have 48GB RAM. This particular server has 96GB so I was surprised by the percentage of memory for the procedure cache.

So, how much of the cache is single use plans?

SELECT  SUM(size_in_bytes)
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
AND usecounts = 1

OK then, what is the makeup of these single use queries?

FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Adhoc'
AND usecounts = 1

I noticed that Red Gate SQL Backup has a lot of queries that look like this…

, a.backup_set_uuid
, a.first_lsn
, a.last_lsn
, a.checkpoint_lsn
, a.database_backup_lsn
, a.media_set_id
, c.name
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
WHERE b.physical_device_name = 'SQLBACKUP_10055B15-EFF3-42C3-9E87-A9DC31E05FE3'
ORDER BY a.media_set_id DESC

All these queries are the same except for the physical_device_name which is the VDI that runs each backup. Given that we're doing lots of transaction log backups this makes sense. The query can be easily parameterised but I don't have the code to SQL Backup so lets create a plan that it can use every time. Also, note that we don't have forced parameterisation turned on on any of our databases.
You can easily check that by using your SSMS server groups and running this against all of them quickly…

SELECT name, is_parameterization_forced FROM master.sys.databases

The correct way to create a plan guide is by creating a query template and then referencing the template in sp_create_plan_guide
This is the example…

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
 , a.backup_set_uuid
 , a.first_lsn
 , a.last_lsn
 , a.checkpoint_lsn
 , a.database_backup_lsn
 , a.media_set_id
 , c.name
 FROM    msdb..backupset a
 INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
 INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
 WHERE   b.physical_device_name = ''SQLBACKUP_10055B15-EFF3-42C3-9E87-A9DC31E05FE3''
 ORDER BY a.media_set_id DESC',
 @stmt OUTPUT, 
 @params OUTPUT;

 EXEC sp_create_plan_guide N'RedGateSQLBackup', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)';

This is all well documented in Books Online… http://msdn.microsoft.com/en-us/library/ms179880.aspx
The next segment to this will be to cleanup the old query plans.


Cleaning up old single use query plans from the cache

After identifying the queries for which we want to create a plan guide, we could just leave it there and the old plans will age out naturally or we could clean them out ourselves. The command to clean out the procedure cache is…


I would be very careful using this command because if you omit the plan handle, you flush every plan from the cache and this would be disasterous on a very busy system. From then on every query submitted needs to be compiled from scratch which is likely to drive your CPU through the roof.

The plan handle is obtained from this query… (Red Gate SQL Backup Query Plan example)

FROM sys.dm_exec_cached_plans AS cp
WHERE objtype = 'Adhoc'
AND usecounts = 1
'SELECT TOP 1 a.type%backupset%'

If you have lots of plans to flush, it would take a while doing each one individually so instead let's build a query string to execute.
One problem here (if you try this out before reading on) is that the plan handle is varbinary(64) and you can't build a query string with mis-matched data types. Converting varbinary to varchar can be a challenge, especially if you search the internet looking for answers. Some of the answers are really scary. However, SQL Server has a built in function to do this for you… sys.fn_sqlvarbasetostr. Use this query to double check that your plan handle in dm_exec_cached_plans matches the one produced by the function and then when you're happy use the next step to flush your plans.

SELECT sys.fn_sqlvarbasetostr(cp.plan_handle)
, *
FROM sys.dm_exec_cached_plans AS cp
WHERE objtype = 'Adhoc'
AND usecounts = 1
'SELECT TOP 1 a.type%backupset%'
ORDER BY [text]

Now, this would be one of the few times I recommend the use of a cursor but this will build your query string and execute it for you. If you want to exec one or two manually, you can comment out the EXEC and uncomment the PRINT and use the result

DECLARE @plan_handle NVARCHAR(max), @sql VARCHAR(500)
DECLARE cached_plans CURSOR
SELECT sys.fn_sqlvarbasetostr(cp.plan_handle)
FROM sys.dm_exec_cached_plans AS cp
WHERE objtype = 'Adhoc'
AND usecounts = 1
'SELECT TOP 1 a.type%backupset%'

OPEN cached_plans
FETCH NEXT FROM cached_plans INTO @plan_handle
WHILE ( @@fetch_status <> -1 )
IF ( @@fetch_status <> -2 )
SELECT @sql = 'DBCC FREEPROCCACHE (' + @plan_handle + ') WITH NO_INFOMSGS'
EXEC (@sql)
--PRINT (@sql)
FETCH NEXT FROM cached_plans INTO @plan_handle
DEALLOCATE cached_plans


SQL Server & Memory

On a 32-bit system there is a maximum of 4GB of virtual address space (VAS). This is made up of: 2GB for the kernel and 2GB user mode for the Buffer Pool minus the Mem-To-Leave amount. Not all the memory is committed at start-up. The Mem-To-Leave is an area in the user mode portion of the 4GB address space that is reserved only at start-up and then released after start-up, so that external items to SQL Server can have contiguous blocks of memory to draw upon. This is typically extended stored procedures, linked servers, SQLCLR etc. However, this area of memory is not guaranteed to be contiguous. The Mem-To-Leave amount is calculated using the following formula:

(Stack Size x Max Worker Threads) + -g startup parameter.

The default setting for Max Worker Threads (in 2005/2008) is 0 which lets SQL Server allocate this value based on the number of CPU’s. In SQL 2000 it was set at 255.

Default thread stack size is 512k (512k on x86 and 2Mb on x64)

Run the following query to confirm both these running values...

SELECT  max_workers_count
      , stack_size_in_bytes / 1024 AS stack_size_kb
FROM    sys.dm_os_sys_info ;
The default setting for –g parameter is 256MB. If it is set to more than 50% of VAS, it is ignored. On a 32-bit dual proc system, the calculation would be...

Mem-To-Leave = StackSize x MaxWorkerThreads + 256MB
= (512k x 256) + 256MB
=  128MB + 256MB
= 384MB

Now, calculate how much physical memory SQL Server has...

2048MB VAS – 384MB Mem-To-Leave = 1664MB

This will explain why on a 32-bit SQL Server with 4GB RAM that SQL will max out at approx 1.6GB. If there is less than 2GB physical memory on the server then SQL Server will reserve that amount – not more than it actually has. The buffer pool cannot reserve more physical memory than actually exists on the server. The VAS for the kernel is still 2GB.

With /3GB or /USERVA the user VAS is increased to 3GB (or a user defined amount with USERVA) and the kernel is restricted to 1GB. This is done in the boot.ini file or in Windows 2008 using BCDEdit.exe as there is no boot.ini in Windows 2008. Using the /3GB switch will increase memory to things like the procedure cache. This doesn’t change the Mem-To-Leave calculation but SQL Server gets 3GB minus Mem-To-Leave. This can cause problems for applications that require memory other than SQL Server which they would acquire from the Mem-To-Leave region if there isn’t enough free contiguous memory available.

An example of this was Red-Gate SQL Backup wouldn’t run on one of our systems with the /3GB switch enabled but once it was removed – no problems. There is no way to defragment the Mem-To-Leave region.

The maximum virtual address space on a 64-bit Windows server it is 8TB. This is a Windows limitation as the actual amount is 16EB. However, no Windows server supports more than 2TB of RAM. Note that 64-bit does not have a Mem-To-Leave as there is no need because of the much increased VAS. Again, the kernel addressable space is also 8TB.

The following query will show the amount of physical memory on the server and the maximum virtual address space...

SELECT  physical_memory_in_bytes / 1024 / 1024 AS physical_memory_mb
      , virtual_memory_in_bytes / 1024 /1024 AS max_possible_virtual_memory_mb
FROM    sys.dm_os_sys_info ;

The CTE query below will show the size of the Mem-To-Leave VAS and the largest available block in the Mem-To-Leave. If the largest block free is less than 4MB the server is considered to be experiencing VAS memory pressure.

WITH    VASummary ( Size, Reserved, Free )
          AS ( SELECT   Size = VaDump.Size
                      , Reserved = SUM(CASE ( CONVERT(INT, VaDump.Base) ^ 0 )
                                         WHEN 0 THEN 0
                                         ELSE 1
                      , Free = SUM(CASE ( CONVERT(INT, VaDump.Base) ^ 0 )
                                     WHEN 0 THEN 1
                                     ELSE 0
               FROM (SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size
                                  , region_allocation_base_address AS Base
                          FROM      sys.dm_os_virtual_address_dump
                          WHERE     region_allocation_base_address <> 0x0
                          GROUP BY  region_allocation_base_address
                          SELECT    CONVERT(VARBINARY, region_size_in_bytes)
                                  , region_allocation_base_address
                          FROM      sys.dm_os_virtual_address_dump
                          WHERE     region_allocation_base_address = 0x0
                        ) AS VaDump
               GROUP BY Size
    SELECT  SUM(Size * Free) / 1024 AS total_available_mem_kb
          , CAST(MAX(Size) AS INT) / 1024 AS max_free_size_kb
    FROM    VASummary
    WHERE   Free <> 0 ;


This query below shows memory usage. It also shows available_virtual_memory_mb to show you how much virtual memory SQL Server has left (if you suspect you are getting low). SQL 2008 only

SELECT  total_physical_memory_kb / 1024 AS total_physical_memory_mb
      , available_physical_memory_kb / 1024 AS available_physical_memory_mb
      , system_cache_kb / 1024 AS system_cache_mb
      , ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS kernel_pool_mb
      , total_page_file_kb / 1024 AS total_virtual_memory_mb
      , available_page_file_kb / 1024 AS available_virtual_memory_mb
      , system_memory_state_desc
FROM    sys.dm_os_sys_memory

Total memory used by SQL Server – this is for SQL2008 only

SELECT  (SUM(virtual_address_space_committed_kb)
        + SUM(locked_page_allocations_kb) 
        + SUM(multi_pages_kb)) / 1024 AS total_sql_memory_usage_mb
FROM    sys.dm_os_memory_nodes ;

On systems with more than 4GB RAM, PAE gives access to the additional physical memory. PAE is enabled in the boot.ini file and it has nothing to do with AWE or /3GB. Individually an application cannot access all this extra memory given by PAE but in totality they can. AWE is a Windows API mechanism that allows an application like SQL Server to access the additional memory over the 4GB VAS, provided by PAE. Memory allocated with AWE is not part of the processes working set so it cannot be paged out. To enable AWE you must grant the “Lock pages in memory” priviledge to the SQL Server service account. This priviledge is granted automatically if the service account is running under the local system account. Also, using sp_configure set “AWE Enabled” to 1. On 64-bit systems the AWE mechanism is still used however it is known as Locked Pages. The AWE setting in sp_configure is ignored on 64-bit systems.

The following query shows how much memory is allocated through AWE

SELECT  SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM    sys.dm_os_memory_clerks ;

Check SQL Server errorlog to see a message that AWE can be used.

AWE extends database pages in the buffer pool only. Other memory objects in 32-bit SQL Server like the  Procedure Cache cannot use AWE memory. Technically, single pages allocated for the Procedure Cache come from the buffer pool. This is why the Mem-To-Leave area is still important on 32-bit systems with more than 4GB RAM. Virtualalloc() is the routine that SQL Server uses for allocating memory. For your standard database page that is 8k, single page allocator is used and this comes from the buffer pool. For multi page allocations, that is greater than 8k, SQL uses the multi page allocator. These pages are allocated from outside the buffer pool – the mem-to-leave area.

The following query show the top 10 consumers of buffer pool memory

      , SUM(single_pages_kb) AS buffer_pool_consumers_kb
FROM    sys.dm_os_memory_clerks
ORDER BY SUM(single_pages_kb) DESC ;

The following shows how much memory is allocated through the multi-page allocator. Remember that the multi-page allocator allocates memory from outside the buffer pool.

SELECT  type
      , SUM(multi_pages_kb)
FROM    sys.dm_os_memory_clerks
WHERE   multi_pages_kb <> 0
GROUP BY type ;

The amount of reserved memory on a 32-bit system without AWE is the visible amount that can actually be accessed.  The visible amount also equals the Target amount, the amount of memory SQL Server would like to use.  The committed amount will increase as physical memory is accessed. With AWE enabled, the target amount will increase to include the amount of AWE memory.  The visible counter will stay the same.Visible always equals target on 64-bit. The following query shows these amounts.

SELECT  ( bpool_committed * 8192 ) / 1024 / 1024 AS buffer_pool_committed_mb
      , ( CAST(bpool_commit_target AS BIGINT) * 8192 ) / 1024 / 1024 AS buffer_pool_target_mb
      , ( bpool_visible * 8192 ) / 1024 / 1024 AS buffer_pool_visible
FROM    sys.dm_os_sys_info ;

The following query will show all memory allocations. Look at the single_pages_kb column where the value is greater than 0 to see what processes are allocated memory from the buffer pool.

FROM    sys.dm_os_memory_clerks
WHERE   ( single_pages_kb > 0 )
        OR ( multi_pages_kb > 0 )
        OR ( virtual_memory_committed_kb > 0 ) ;

SQL Server myth-busters

Many thanks to Bob Ward at Microsoft for reviewing this article for accuracy.


Edition Upgrade on SQL2005

Edition upgrades on SQL2005 are a completely different kettle of fish compared to SQL 2000.

Whereas in 2000, you pop the cd in the drive and it recognises that you can upgrade the edition and it is all over in a minute, 2005 requires you to perform the upgrade from the command line. You can find the command in books online. Look up "Install SQL Server from the Command Prompt" then go down to parameters and click on SKUUPGRADE. Here is the command to upgrade the default instance of the database engine...


Note that the commands are case sensitive.

Another point to note is that after the instance is upgraded the installation is now at RTM version. So you have to apply any service packs or hotfixes again.


Adding DTS Support to SSMS

I have done this on my recent rebuild of my laptop at work so know that this does work. SQL 2000 tools are no longer required. The following add-ons are required to provide DTS support in SSMS 2008...

1. SQL 2000 DTS Designer components ( Select Feature Pack from this link http://technet.microsoft.com/en-nz/sqlserver/bb671253(en-us).aspx )

2. SQL 2005 Backward Compatibility Components ( Select SQL 2008 Feature Pack from this link http://technet.microsoft.com/en-nz/sqlserver/bb671408(en-us).aspx )

Once these files have been downloaded they must be installed in this order. If the backward compatibility components were installed first, you can re-run the install and choose repair.

3. Modify the path statement so that the SQL2000 tools are before the SQL2008 tools...

e.g C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (should be first on the path before SQL 2008)

Stop and re-start SQL Server to have the change take effect.

SQL Server setup failure due to Cluster Resource failure

When running SQL Server 2008 setup and during the installation a dialog box appears to say that installation failed because it couldn't bring a cluster resource online then do not despair. This failure is quite common apparently and there is an easy fix.


Actually, because the wizard got to the part where you assign the cluster resources means that the install has completed. This is the last step in Setup. This is why you shouldn't uninstall because you will end up making your installation worse. This error has occurred because the cluster service could not be authenticated by SQL due to NTLM and no Kerberos. Because it failed trying to bring the SQL Server cluster resource online means that the SQL Agent resource was not created. So, we have to put in a workaround so that the SQL cluster resource will start and then manualy create the SQL Agent resource.

This is fixed with 4 steps.

1. Add a registry key to disable loopback check. This will enable Kerberos locally.

The registry key is: HKLM\System\CurrentControlSet\Control\LSA

Add a new DWORD "DisableLoopbackCheck" with a value of 1

Now, the SQL Server resource will come online :-)

2. Create the SQL Agent Resource type. Using the Cluster Management snap-in, right click on the newly created cluster, go properties, resource types and then add.

Find the resource DLL at... C:\Windows\System32\SQAGTRES.DLL For the Resource Type name, it must be typed exactly correct (case sensitive also) or the resource will not come online. The correct name is SQL Server Agent

3. Once the resource type is added, the resource can be added. In the actions pane, click on Add a Resource and select the Resource Type just created. Select Properties of the new resource and on the Dependencies tab add SQL Server in as a dependency. On the Properties tab set the Virtual Server Name and also the Instance Name.

Now the resource should come online :-)

4. 1 more step... cos the system thinks that the installation failed then if you try to patch then that will fail too cos the Setup Configuration State is showing failed. Open regedit and go to...

HKLM\Software\Microsoft\Microsoft SQL Server\<instance>\ConfigurationState\

Change the value of all these keys from 2 to 1. IMPORTANT - this is a supported fix by Microsoft.

One way to avoid this from the beginning is to make sure that static ports have a SPN set. See another article for setting SPN's.

Thanks to Shon Hauck - Microsoft Senior Escalation Engineer presenting this at PASS 2009.


Rebuilding system databases on SQL Server 2005 (or adding additional components)

SQL server is corrupt - master is damaged. You didn't do the original install and you have to supply the media for the repair.

In the situation I'm thinking of, SQL Server was installed as part of the server build or, it might have been installed using the DVD (unlikely!) or even from a network share but either way you're now doing it from different source media e.g. you are supplying the installation media via the two files downloaded from Microsoft VLSC. First file is the Server components (or CD 1) and the second file is the Tools (or CD 2). You must follow this process or you cannot proceed.

Extract the first file to a folder called Servers

e.g. C:\Temp\SQL2005CD\Servers

Extract the second file to a folder called Tools. This must be at the same level as the Servers folder.

e.g C:\Temp\SQL2005CD\Tools

This is documented in a Microsoft knowledgebase article here.

Rebuilding the system databases...

If you need to know the edition of SQL Server that you are going to rebuild, then look in the registry key HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup and there is a key called "Edition"

Run the following command (in a cmd window) to rebuild the system databases using your source (case sensitive, all options req'd):

start /wait C:\Temp\SQL2005CD\setup.exe /qb INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

I recommend the use of the qb switch rather than the qn switch. qb will display dialog boxes during the process so you're not running blind. Also, I found that I had to answer questions during the rebuild so unless you're providing an answer file then this is another good reason to run the qb switch.

When you start the repair process you get an error partway through saying cannot find file SqlRun_SQL.msi. Upon checking the folder you can see the file there. This is all to do with the location used for the installation. This is stored in the following registry key... HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\<Product_Code>\SourceList\LastUsedSource

Step through the Product_Code folders to find the one for SQL Server 2005 database engine installation and look at the LastUsedSource key in the SourceList.

Open the key and change the location to that location that is listed for the key "PackageName". e.g. SqlRun_SQL.msi is usually in \Setup folder so you would put C:\Temp\SQL2005CD\Setup\

If there is n;1; then leave it there, just append to the end of that. If there is a NET folder also, open that and change the item 1 to the same source location.

This will now allow the process to run without error.


Encrypting SSMS connections with certificates

We had a requirement for PCI compliance that all admin connections to SQL Server must be encrypted. I took this a little bit further to ensure that this requirement was easily enforced with Login triggers.

In our environment, we have a certificate server as part of SCOM so I use this to generate the certificates. The Trusted Root certificate should already exist on the server as part of the SCOM component install which would have been done before the server was handed over to the DBA's.

Generating the Certificate:

  1. RDP onto the certificate server
  2. Open IE and browse to http://<server_name>/certsrv and this should open Microsoft Certificate Services
  3. Click on "Request a certificate"
  4. Click on "Advanced Certificate Request"
  5. Click on "Create and submit a request to this CA"

Fill in or select the following details;


Click "Yes" to generate the certificate.

Now the Certificate Pending page should be displayed. The certificate now needs to be issued. You may require someone else do this part for you unless you are a local administrator on the server. Note the request ID just in case there are many certificates waiting to be issued.

To Issue the Certificate:

Run up a MMC

Add Certificate Authority snap-in

In the left pane, open the Certificate Authority all the way to Pending Requests. In the right pane, right click on your requestID and select All Tasks > Issue

Go back to the browser window and select Home in the top right hand corner.

Click on the "View the Status of a Pending Certificate Request". You should see your Server Authentication Certificate. Click on this. Then Click on "Install this certificate". The certificate was installed onto the server you are logged onto in your terminal session.

Now back to the MMC and add the Certificates snap-in to "My user account"

In the left pane, open Certificates - Current User > Personal > Certificates and you should see your certificate in the right pane. You need to export this certificate so it can be moved to your server and installed to SQL Server. Right click > All Tasks > Export

Run through the wizard selecting the following...

Yes, export the private key (not default)

Personal Information Exchange should be the only option. Select the following options...

Tick Include all certificates in the certificate path if possible (not default)

Un-Tick Enable strong protection (not default)

Tick Delete the private key if export is successful (not default)

Enter a password of your choice

Select a path to export the certificate to, including a name for the certificate. Next. Finish. You should get a response back "The export was successful"

Now, copy that certificate over to the server that you want this to be installed to.

Before closing the MMC,delete the certificate from your personal store that you just created.

Adding the Certificate to SQL Server

RDP onto the SQL Server.

Run the MMC as the SQL Server service account.

1. runas /user:<domain\account> mmc

If your accounts don't allow interactive login then this will not work and generate an error saying "the user has not been granted the requested logon type at this computer". Making a local admin will still not work for this.

2. Temporarily make your SQL Server service account a local administrator. Using Windows Explorer, browse to C:\Windows\System32 and shift-right click on mmc.exe to "run as a different user". Enter the details of the account used to run the SQL Server database engine.

Once open, add the Certificates MMC snap-in as "My user account" which is the correct option as you are currently running as your SQL service account

Open the Certificates folder and right click on the Personal folder and select All Tasks > Import to start the import certificate wizard.When you get to the password option, enter the same password that you put when exporting the certificate. Un-tick the box "Enable strong private key protection".

Now the certificate is installed for the SQL Server service account and will be used next time SQL is stopped and then re-started. This is instead of using the Self Generated Certificate.

You should also see that by this method the root certificate will aalso be added into the personal store. This can be safely deleted.

Open the SQL Server log and you should see ... The certificate [Cert Hash(sha1) "D86CD68AB1D37403E145673586EFD9999B444694"] was successfully loaded for encryption.

instead of... A self-generated certificate was successfully loaded for encryption.

Encrypting connections to SQL Server

You can selectively encrypt connections now or you can encrypt all connections. We only want to encrypt admin connections only.

Open SSMS and register the server. As part of the registration process, click on the Connection Properties tab and check the box to Encrypt Connection.You can verify this is working so far by running the following query...

SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID

The encrypt_option column should read "TRUE".

Now, this needs to be enforced as still anyone can connect unencrypted. This is achieved using a logon trigger.

USE master ;
            FROM    sys.server_triggers
            WHERE   name = 'deny_nonencrypted_ssms_sessions' ) 
         DROP TRIGGER deny_nonencrypted_ssms_sessions ON ALL SERVER ; 
         -- DISABLE TRIGGER deny_nonencrypted_ssms_sessions ON ALL SERVER 
CREATE TRIGGER deny_nonencrypted_ssms_sessions ON ALL SERVER
   IF IS_SRVROLEMEMBER('sysadmin') = 1
         IF EXISTS ( SELECT  *
                     FROM    sys.dm_exec_sessions AS s
                             INNER JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
                     WHERE   s.session_id = @@SPID
                     AND ( s.program_name LIKE 'Microsoft SQL Server Management Studio%'
                           OR s.program_name = 'SQL Query Analyzer'
                     AND c.encrypt_option = 'FALSE' ) 

If you want to capture all connections that are not encrypted, take out the IF IS_SRVROLEMEMBER clause. One caveat here is that to be able to trip the trigger, the login must have VIEW SERVER STATE permission.

To over-ride the trigger, connect using the DAC (Dedicated Admin Connection).

Optional extra:

While you have the MMC open as the SQL Service account, you can add the SQL Configuration Manager snap-in. Open SQL Server Netwrok Configuration and right click on "Protocols for MSSQLSERVER" and select properties. The second tab is Certificate and here you can also add the certificate, so that it is visible. Adding the certificate in here writes the thumbprint of the certificate to a registry key...

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib and the key is called Certificate.

You can actually enter this value in manually... open the certificate and on the details tab of the certificate copy the hash value for the thumbprint. Paste this into notepad and then remove all the spaces from between the characters. Please also note that there is a leading space that needs to be removed which you cannot see.

Now, you can query for the certificate rather than looking the entry in the SQL Error Log.

EXEC sys.xp_regread N'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib', N'Certificate'

No certificate returns NULL, a certificate returns the thumbprint.

Remember, it is not necessary to do this but I find it makes the certificate more visible.

XML Sample Queries... here


© Brent McCracken 2009-2011