Using a self-signed SSL certificate with SQL Server

Learning to configure SQL Server to use a self-signed SSL certificate was not really part of my training plan. However, Data Explorer required SSL encryption to connect to SQL Server. On the same day I managed to make it work, the DE team released an update that waives this requirement …* sigh* …

The steps described below are just for development and tests, to connect to a local instance of SQL Server. Best practices for SSL encryption and server security may look different.

Here is a summary of how I did it on my machine (SQL Server 2008, 2012 – Express editions too -Windows 7 Ultimate).

Short version

To configure SQL Server to use a self-signed SSL certificate, you will have to:

  1. Create a self-signed certificate
  2. Set permissions for this certificate
  3. Configure SQL Server to use this certificate

Point number 2 is the one I had most problems with. The SQL Server log had entries like these:

  • “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.”
  • “Unable to load user-specified certificate …”
  • “… Unable to initialize SSL support. Cannot find object or property.“

Solution: Grant SQL Server rights to read the private key for the certificate.

All steps are described below.

Create a certificate

With IIS

If you have IIS on your machine:

  • Start IIS Manager
  • Go to Server Certificates
  • Right Click > Create Self-Signed Certificate

With makecert

The process as explained by SQL Server BOL uses the makecert command line utility. Since I could not find makecert , until I had solved my problem, I did not follow BOL. You can find the tool here.

Set permissions for the certificate

Find the service account name for your SQL Server instance

  • Start SQL Server Configuration Manager
  • Go to SQL Server Services
  • Select your instance
  • Right-click Properties
  • Copy the account name for later re-use

Launch the Certificates snap-in

One way to manage your certificates is to

  • Launch MMC (type mmc.exe in the Windows search bar)
  • Go to File > Add / Remove Snap-in …
  • Select Certificates ( when prompted choose Computer Account)

Grant SQL Server rights to read the private key for the certificate.

  • In MMC, go to Certificates (Local computer) > Personal > Certificates
  • The certificate should be listed there. ( If  you created the certificate using makecert, you may have to import it.)
  • Right click > All Tasks > Manage Private Keys
  • Add the service account for your instance of SQL Server
  • Give the service account Read permissions

Configure SQL Server to use this certificate

  • Start SQL Server Configuration Manager
  • Go to SQL Server Network configuration
  • Select your instance
  • Right-click > Properties > Certificate tab
  • Choose the certificate you just created
  • Restart the service

Conclusion

I tried to make this post as detailed as possible, but not too detailed. Any questions, suggestions, or corrections? Use the comments below.

Advertisements

Analytic functions in SQL Server 2012–Part II –

For the rest of this article, suppose you have a table describing events, with the following columns:

  • EmployeeID: the ID of the employee who triggered the event
  • ItemID: the ID of the item for which the event was triggered
  • EventDateTime: when the triggered occurred.

FIRST_VALUE / LAST_VALUE

These functions will get the first/ last value within the specified PARTITION. “First” and “last”  will be defined according to the ORDER specified in the OVER clause.

To get the ID of the employee who created an item (first event), you could use the following expression:

FIRST_VALUE(EmployeeID) OVER (PARTITION BY ItemID ORDER BY EventDateTime ASC)

The expression will return the value of EmployeeID for the row that has the lowest EventDateTime, amongst every rows having the same value for ItemID.

LAST_VALUE fits a similar role, but beware of the default window frame:

When no ROWS or RANGE clause has been specified in the OVER clause, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used.

Does anybody see any use for using LAST_VALUE with this window frame?

LAG / LEAD

LAG and LEAD allow you to get information from the n-th row before or after the current “row”.

The following expression returns the date time of the next event for the current ItemID:

LEAD( EventDateTime, 1, GETDATE() )  OVER (PARTITION BY ItemID ORDER BY EventDateTime)

LAG and LEAD accept no window frame (no implicit ROWS or RANGE clause).

Are these functions deterministic?

When you use these functions, remember the following: if the value you wish to return is not included in the ORDER BY clause, rows with different values may have the same order. In that case, you cannot guarantee which value will be returned.

Consider the following example:

WITH DATA (partitionID, orderingValue, Value) AS (
SELECT 1, 100, ‘VAL1’ UNION ALL
SELECT 1, 100, ‘VAL2’ UNION ALL
SELECT 2, 100, ‘VAL2’ UNION ALL
SELECT 2, 100, ‘VAL1’
)
SELECT
*
INTO #TMP
FROM DATA;

SELECT *
, FIRST_VALUE(Value) OVER (PARTITION BY partitionID ORDER BY orderingValue)
FROM #TMP;

CREATE CLUSTERED INDEX myTempIndex
ON #TMP (partitionID, orderingValue, Value);

SELECT *
, FIRST_VALUE(Value) OVER (PARTITION BY partitionID ORDER BY orderingValue)
FROM #TMP;

DROP INDEX myTempIndex ON #TMP;
DROP TABLE #TMP;

Creating a clustered INDEX on the table changed the outcome of the query.

Even without changes to an INDEX, I suspect a change in an execution plan may also lead to such changes.

Analytic functions in SQL Server 2012 – Part I – Distributions

In this article, I take a look at the analytic functions in SQL Server 2012 to handle frequency distributions.

CUME_DIST

The CUME_DIST function returns the percentage of records that are of same or lower order than the current record.

The expression:
CUME_DIST() OVER(ORDER BY MyValue)
Is equivalent (neglecting precision) to :
1.0 * COUNT(*)
OVER (ORDER BY MyValue
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
/ COUNT(*) OVER ()

Possible scenario: calculate the percentage of households whose income is not greater than the current one.

PERCENT_RANK

The PERCENT_RANK rank is similar to the CUME_DIST function.

The expression:
PERCENT_RANK() OVER(ORDER BY MyValue)

Is equivalent to (neglecting integer division) :

(  RANK() OVER (ORDER BY MyValue) –1      )
/ ( COUNT(*) OVER ()         –1      )

Possible scenario: for each household, calculate the percentage of the other households that earn less than the current one.

PERCENTILE_DISC

Returns the smallest value such that CUME_DIST is equal to or greater than the provided probability.

PERCENTILE_DISC (0.4)
WITHIN GROUP ( ORDER BY MyValue ASC )
OVER()

A few remarks:

  • NULL values are ignored, although this is not the case for the CUME_DIST function;
  • The OVER clause is mandatory, although it may be empty;
  • No ORDER BY is allowed in the OVER clause; there is a specific WITHIN GROUP clause to specify the ordering of the partition.

Possible scenario: what is the income under which 10% of households fall?

PERCENTILE_CONT

This is an interpolated version of PERCENTILE_DISC. It shares the same syntax. The same remarks as above apply.

%d bloggers like this: