How to Create SSL Certificate for SQL Server

Here is a quick way that I use to create a server-level certificate which allows me to use SSL encryption option in SQL Server Configuration Manager. If you have any questions throughout this process, feel free to contact me here. I will be more than happy to help you out.

Copy the below (in between the ==) into a blank textfile. To find your Fully Qualified Domain Name (or FQDN), open the Command Prompt (Run –> Type cmd then press ENTER), at the prompt type:

ipconfig /all

Your FQDN will be in the following format:

“Host Name.Primary Dns Suffix”

Take that FQDN and replace the MY.FQDN.COM with your FQDN below

========COPY=============
[Version]
Signature = $Windows NT$

[NewRequest]
Subject = “CN=MY.FQDN.COM
Exportable = TRUE
KeyLength = 2048
KeySpec = 1                         ; AT_KEYEXCHANGE
KeyUsage = 0xA0                     ; Digital Signature, Key Encipherment
MachineKeySet = True                ; The key belongs to the local computer account
ProviderName = “Microsoft RSA SChannel Cryptographic Provider”
ProviderType = 12
SMIME = FALSE
RequestType = PKCS10
========END COPY==========

Then save it as a .inf file. For example, MyServer.inf

*** Make sure you change the “Save as type” option to “All Files.” The default is “.txt” and if you don’t change it to “All Files” you will end up with a file like “MyServer.inf.txt” which will not work.

Next, open the Command Prompt and type the following:

certreq -new C:\certificate\MyServer.inf (see screenshot below)

How to Create SSL Certificate for SQL Server Cmd Prompt New
Once you hit [Enter], a pop-up will open and prompt you to pick a location to save the hash file. Just choose the same folder that your MyServer.inf file is in to lessen confusion.

Next, if you have a person who acts as the CA (Certificate Authority) or is the single point of contact for all certificate requests then all you have to do is rename the .inf file to a .csr file and send it to them as an attachment in an encrypted/secured email.

They will take that hash output, fill out a form, and submit it to the CA. After a few days (or weeks) they will receive an email with the complete certificate hash and they forward that to you.

I have worked in environments where I had to email the single point of contact as well as fill out the form myself. If you are required to go to https://ca-27.csd.disa.mil/ca/ and submit the request for a certificate yourself, and need help…check out instructions on how to do that below:

How to Request a DoD Server Certificate

How to Request a DoD Server Certificate

I have worked in many government facilities throughout my career and most recently I was in charge of securing a couple SQL Server database servers. One of the items on the “checklist” to secure was installing a server-level DoD SSL certificate. By installing this certificate, it would allow me to enable “Force Encryption” in SQL Server Configuration Manager.

Work environments differ. Some require you to do everything. Some require you to do a certain point before passing it to a point of contact within the organization. If you are in charge of installing a DoD SSL Certificate and have a CAC card, then hopefully this blog will help you save time and headache! (feel free to contact me if you have any issues)

You will first have to submit your hash at the following link to get a “request ID”, which you will need to fill out the form.

Visit: https://ca-27.csd.disa.mil/ca/

**UPDATE: New DoD PKI SHA-256 CAs have been released. Enrollment pages for these CAs are available at: https://ee-id-sw-ca-37.csd.disa.mil

1. You click on the link above, and then choose “New 2048-bit SSL Enrollment form” option (see screenshot below)

DoD Certificate Manager Homepage

2. Then, choose PKCS#10 for Certificate Request Type.

3. Cut and paste your has in the “Certificate Request” textbox.

4. Type out the FQDN of your server in the “General Name Value” textbox.

5. Enter in your Name, Email, Phone in the “Requestor Information”

6. Click Submit.

DoD Certificate Manager Profile Page

The following screen will display a “Request ID.” Copy that Request ID and paste it in the Certificate Request form. For more details on how to install the certificate, check out my blog post here:

How to Create SSL Certificate for SQL Server

We Couldn’t Verify the Product Key, Hyper-V

If you want a step-by-step walkthrough on setting up a complete Hyper-V networked lab environment consisting of a Domain Controller and five domain member machines, then check out this great in-depth article here.

The lab that you setup using the link above will be used for the “Exam 70-462: Administering Microsoft SQL Server 2012 Databases book by Orin Thomas, Bob Taylor and Peter Ward. Now, of course, not all guides/books are 100% error free. You can find the “errata” of the book here.

When you are creating your Hyper-V virtual machines, the book says to keep the 512MB RAM default setting. But, when I created a virtual machine with the 512 MB RAM default, I kept getting “we couldn’t verify the product key…”

After MULTIPLE times at trying to fix the issue, I increased the RAM to 1 GB and the install worked. Just a reminder.

How to Test Connection to SQL Server Using UDL File

This past week at work we had an application go down. The owner of that application rushed over to me and said the database server was down. I logged into the database server and verified that SQL Server service was up and running. To further allay his concern that the database server was blocking his application from making connections, I logged into the application server and quickly created a UDL file to verify the connection. Below is a step-by-step on how I did that so you can use it in case you run into a similar situation where you have to verify if a server can make a successful connection to a SQL Server database server.

How to Test Connection to SQL Server

First, create a blank text file using Notepad and save it as whatever name you want with a “.udl” extension. I used “DataConnectionTest.udl.” Now make sure to click on the drop down and choose “All Files” instead of “.txt” (see snapshot below)

test-data-connection

 

After saving it as a “.udl” you will see this icon (see screenshot below):

Test Data Connection Icon

Double-click the icon and the main UDL window will open. Click on the Provider tab and choose “Microsoft OLE DB Provider for SQL Server.”

Test Data Connection Provider

Click Next, OR click on the “Connection” tab

Test Data Connection Connection

For “1. Select or enter a server name:” type out the sql server database server name. To save time, I would type out the server name instead of clicking the drop down option.

For “2. Enter information to log on to the server:” choose whichever option you need. In my example I chose Windows NT Integration security

At this point, you can click on the “Test Connection” button, or click the drop down under #3 and choose what database you want to connection to. Once you click on “Test Connection” you should see the “Test connection succeeded.” pop box.

Test Data Connection Test Connection

This is a very quick and easy way to verify connection to a SQL Server database without any special tools or software.

Perfmon Create New Data Collector Set Grayed Out (FIX)

Recently I wanted to create a new data collector in Windows Performance Monitor (perfmon) and the option to hit “Next” was grayed out (see screenshot below)

Perfmon Greyed Out

I checked Services and noticed the Task Scheduler service was disabled and the option to change it to start was grayed out. Next, I went into the Windows Registry (regedit.exe) and navigated to; HKEY_LOCAL_MACHINE –> SYSTEM –> CurrentControlSet –> servives –> Schedule

…and noticed the Start parameter was set to a value of 4 (see screenshot below).

task-scheduler-4

 

I changed it to a value of 2 (see screenshot below)…

Task Scheduler 2

…restarted the server and launched Windows Perfmon. Lo and behold, the “Next” button was not grayed out and I could create a new data collector set! (see screenshot below). By the way, I am running Windows Server 2008 R2 Enterprise and I had to restart my server in order for the regedit modification to take effect.

Perfmon NOT Greyed Out

BACKUP DATABASE testDB TO DISK=’NUL:’

A very quick and easy way to see the how fast your hard disk is, is to run:

BACKUP DATABASE testDB TO DISK=’NUL:’

Don’t mistake the ‘NUL’ for NULL. They are two separate things. The ‘NUL:’ is considered a device and anything written to ‘NUL’ is discarded.

I downloaded and installed the StackOverflow database on my laptop (if you want to install the StackOverflow database on your machine to play around, check out Brent Ozar’s instructions on how to do it here). My laptop is running Crucial MX200 1TB SSD. My throughput is 490.491 MB / sec (see snapshot below).

backup-database-to-nul

 

Caveats

SQL Server will treat the ‘NUL:’ LOG backup statement as a valid backup. Meaning, if you have a previous LOG backup and then run ‘NUL:’ backup it will break your LOG backup chain and you will get Msg 4305, Level 16, State 1, Line 1 error as well as Msg 3013, Level 16, State 1, Line 1 error (see screenshot below).

restore-from-nul-fails

 

You can get around this by using the COPY_ONLY option (see screenshot below)

restore-from-nul-with-copyonly-1

 

That way it doesn’t affect the backup chain and you still get the write speed for your disk! I have included the code snippet below if you want to play around with it!

--take FIRST full backup of AdventurWorks2012
BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\backups\AW_FULLBackup_1.bak'

--take FIRST LOG backup of AdventureWorks2012
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\backups\AW_LOG_Backup_1.trn'

--take NUL: LOG backup of AdventureWorks2012 <---THIS WILL BREAK LOG CHAIN
BACKUP LOG AdventureWorks2012 TO DISK = 'NUL:'
--WITH COPY_ONLY

--take 3rd LOG backup of AdventureWorks2012
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\backups\AW_LOG_Backup_2.trn'

--DROP AW Database
DROP DATABASE AdventureWorks2012

--RESTORE from FIRST FULL
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\backups\AW_FULLBackup_1.bak'
WITH NORECOVERY

--RESTORE FIRST LOG
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\backups\AW_LOG_Backup_1.trn'
WITH NORECOVERY

--RESTORE SECOND LOG (will fail)
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\backups\AW_LOG_Backup_2.trn'
WITH RECOVERY

SQLCMD -L Not Listing Your Servers? Try This

I was recently playing around with sqlcmd and wanted to list the SQL Servers on my server. When I ran “sqlcmd -L” I got the below output:

sqlcmd-L not working

I quickly realized that SQL Browser was disabled. Once I enabled it and ran sqlcmd -L, I got the below result:

sqlcmd-L working

Per Microsoft: “SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer” (MSDN)

So sqlcmd uses SQL Server Browser service to gather information on SQL Servers. If SQL Server Browser is off, then you can’t use sqlcmd -L.

I Can’t Start SQL Server Browser – FIX

Recently a developer approached me at work and said, “I Can’t Start SQL Server Browser”

So, I logged into SQL Server Configuration Manager and saw that SQL Server Browser was stopped / off. When I right-clicked the SQL Server Browser to turn it on, I got this:

SQL Server Browser SQL Configuration Manager Nothing

No Start, no Stop…nothing. I clicked on Properties, clicked on the Service Tab and choose Automatic. (See screenshot below)

SQL Server Browser SQL Configuration Manager services Tab

Click Apply and OK. Now when I right click the SQL Server Browser I get all options (see screenshot below).

SQL Server Browser SQL Configuration Manager Start

Hope that helped!

How to Enable or Disable Intellisense in SSMS

If for some ODD reason you want to disable Intellisense in SQL Server Management Studio, there is a very easy way to do so.

How to Enable or Disable Intellisense in SSMS

Open SSMS – Click on Tools, Options, Expand Text Editor, Transact – SQL, and then click on Intellisense (check screenshot below)

Disable or Enable Intellisense in SSMS

It’s as simple as that! If you are trying to enable / disable to refresh the Intellisense cache because you have the red squiggly underline in your T-SQL, check out my post here to show you an easier way to refresh your Intellisense cache in SSMS.

How to Create LOGON Trigger in SQL Server

Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.

For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions.

The script below is what I came up with. It’s fairly straight-forward. At the bottom of the query you will see 2 SELECT statements:

  1. sys.triggers – You will find database level triggers here.
  2. sys.server_triggers – You will find server level triggers here.

Feel free to modify the script below to suit your specific needs.

CREATE TRIGGER limit_sessions_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorText varchar(128)

SET @ErrorText = 'Cannot exceed 20 sessions. Close out older sessions to open newer ones.
    				If you need help, contact your Database Admin.';

IF ORIGINAL_LOGIN()= 'JohnDoe' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'JohnDoe') > 20
	BEGIN
		PRINT @ErrorText
		ROLLBACK;
	END
END;
GO

--Instance/Server level Triggers
SELECT *
FROM sys.server_triggers


--Database Level Triggers
SELECT *
FROM sys.triggers

--Delete Server level Trigger
DROP TRIGGER limit_sessions_trigger
ON ALL SERVER;
GO