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

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

How to Check SQL Server Database Encryption Algorithm

I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers.

I already knew it was AES-256 but IA needed a screenshot as proof. So I wrote this little query to bring back the database name, id, encryption key length and algorithm. I ran it, took a screenshot and sent it to them.

--Bring back database name, id
--key algorithm and key length
select db.name,
e.database_id,
e.key_algorithm,
e.key_length
from sys.dm_database_encryption_keys e
join sys.databases db on db.database_id = e.database_id

How to Create a DISA STIG SQL Server Trace

If you work for the government and have to create a SQL Server Trace that monitors all the trace event IDs that come in the DISA STIGs then you have come to right place! (I feel like I’m hosting an infomercial)

I have implemented this script on numerous database servers during my professional career. It’s very straight-forward. There are a few places that you need to provide parameter information, for example the drive location you want SQL Server to save your trace files to. Also, this script is setup to override and shut down the server if for some reason SQL Server Trace has a problem writing to the trace file. (per STIG requirement)

You can run the script below and be assured that your SQL Server Trace suffices the STIG requirement. If you’d like to know how to stop and delete a SQL Server Trace, read about it here.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_create_STIG_trace]
-- Create the trace
AS
    -- Declare local variables
  declare @rc int
  declare @on bit
  declare @scriptname nvarchar(50)
  declare @tracefile nvarchar(256)
  declare @maxfilesize bigint
  declare @filecount int
  declare @traceid int 

  set @maxfilesize =100
  set @filecount =100

  -- Trace file name
  set @scriptname = 'cc_STIG_trace_' + REPLACE(REPLACE(CONVERT( varchar(50), getdate(),126), ':', ''), '.','')

  set @tracefile = 'E:\Trace\' + @scriptname ---need to modify the directory path

  --- Create the trace
    	--- Second parameter 6 indicates: TRACE_FILE_ROLLOVER(2)+SHUTDOWN_ON_ERROR(4)
  
  exec @rc = sp_trace_create @traceid OUTPUT, 6, @tracefile, @maxfilesize, NULL , @filecount
  
  IF (@rc != 0) 
  begin
    GOTO PROC_ERROR
  end

  -- Add Trace Events
  set @on = 1
 
  -- Audit Login
  exec sp_trace_setevent @TraceID, 14, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 14, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 14, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 14, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 14, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 14, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 14, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 14, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 14, 64, @on  -- SessionLoginName
   
  -- Audit Logout
  exec sp_trace_setevent @TraceID, 15, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 15, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 15, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 15, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 15, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 15, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 15, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 15, 64, @on  -- SessionLoginName

  -- Audit Server Starts and Stops Event Class
  exec sp_trace_setevent @TraceID, 18, 10, @on  -- ApplicationName	
  exec sp_trace_setevent @TraceID, 18, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 18, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 18, 21, @on  -- EventSubClass 
  exec sp_trace_setevent @TraceID, 18, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 18, 64, @on  -- SessionLoginName

  -- Audit Login Failed
  exec sp_trace_setevent @TraceID, 20, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 20, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 20, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 20, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 20, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 20, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 20, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 20, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 20, 64, @on  -- SessionLoginName

  -- SP:Starting (Ex)
 	exec sp_trace_setevent @TraceID, 42, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 42, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 42, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 42, 14, @on  -- StartTime
 	exec sp_trace_setevent @TraceID, 42, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 42, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 42, 64, @on  -- SessionLoginName

  -- SP:Completed (Ex)
  exec sp_trace_setevent @TraceID, 43, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 43, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 43, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 43, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 43, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 43, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 43, 64, @on  -- SessionLoginName

  -- Audit Database Scope GDR Event
  exec sp_trace_setevent @TraceID, 102, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 102, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 102, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 102, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 102, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 102, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 102, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 102, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 102, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 102, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object GDR Event
  exec sp_trace_setevent @TraceID, 103, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 103, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 103, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 103, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 103, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 103, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 103, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 103, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 103, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 103, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 103, 64, @on  -- SessionLoginName

  -- Audit AddLogin Event
  exec sp_trace_setevent @TraceID, 104, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 104, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 104, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 104, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 104, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 104, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 104, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 104, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 104, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 104, 64, @on  -- SessionLoginName

  -- Audit Login GDR Event
  exec sp_trace_setevent @TraceID, 105, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 105, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 105, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 105, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 105, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 105, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 105, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 105, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 105, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 105, 64, @on  -- SessionLoginName
   
  -- Audit Login Change Property Event
  exec sp_trace_setevent @TraceID, 106, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 106, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 106, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 106, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 106, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 106, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 106, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 106, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 106, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 106, 64, @on  -- SessionLoginName
   
  -- Audit Login Change Password Event
  exec sp_trace_setevent @TraceID, 107, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 107, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 107, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 107, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 107, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 107, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 107, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 107, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 107, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 107, 64, @on  -- SessionLoginName
   
  -- Audit Add Login to Server Role Event
  exec sp_trace_setevent @TraceID, 108, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 108, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 108, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 108, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 108, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 108, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 108, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 108, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 108, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 108, 64, @on  -- SessionLoginName

  -- Audit Add DB User Event
  exec sp_trace_setevent @TraceID, 109, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 109, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 109, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 109, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 109, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 109, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 109, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 109, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 109, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 109, 64, @on  -- SessionLoginName
   
  -- Audit Add Member to DB Role Event
  exec sp_trace_setevent @TraceID, 110, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 110, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 110, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 110, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 110, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 110, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 110, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 110, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 110, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 110, 64, @on  -- SessionLoginName

  -- Audit Add Role Event
  exec sp_trace_setevent @TraceID, 111, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 111, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 111, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 111, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 111, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 111, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 111, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 111, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 111, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 111, 64, @on  -- SessionLoginName
   
  -- Audit App Role Change Password Event
  exec sp_trace_setevent @TraceID, 112, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 112, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 112, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 112, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 112, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 112, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 112, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 112, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 112, 64, @on  -- SessionLoginName

  -- Audit Statement Permission Event
  exec sp_trace_setevent @TraceID, 113, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 113, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 113, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 113, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 113, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 113, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 113, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 113, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 113, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 113, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Access Event
  exec sp_trace_setevent @TraceID, 114, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 114, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 114, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 114, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 114, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 114, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 114, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 114, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 114, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 114, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 114, 64, @on  -- SessionLoginName
   
  -- Audit Backup/Restore Event
  exec sp_trace_setevent @TraceID, 115, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 115, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 115, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 115, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 115, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 115, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 115, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 115, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 115, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 115, 64, @on  -- SessionLoginName
   
  -- Audit DBCC Event
  exec sp_trace_setevent @TraceID, 116, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 116, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 116, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 116, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 116, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 116, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 116, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 116, 64, @on  -- SessionLoginName
   
  -- Audit Change Audit Event
  exec sp_trace_setevent @TraceID, 117, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 117, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 117, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 117, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 117, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 117, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 117, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 117, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 117, 64, @on  -- SessionLoginName

  -- Audit Object Derived Permission Event
  exec sp_trace_setevent @TraceID, 118, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 118, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 118, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 118, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 118, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 118, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 118, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 118, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 118, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 118, 64, @on  -- SessionLoginName

  -- Audit Database Management Event
  exec sp_trace_setevent @TraceID, 128, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 128, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 128, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 128, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 128, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 128, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 128, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 128, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 128, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 128, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Management Event
  exec sp_trace_setevent @TraceID, 129, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 129, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 129, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 129, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 129, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 129, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 129, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 129, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 129, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 129, 64, @on  -- SessionLoginName
   
  -- Audit Database Principal Management Event
  exec sp_trace_setevent @TraceID, 130, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 130, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 130, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 130, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 130, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 130, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 130, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 130, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 130, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 130, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Management Event
  exec sp_trace_setevent @TraceID, 131, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 131, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 131, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 131, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 131, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 131, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 131, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 131, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 131, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 131, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 131, 64, @on  -- SessionLoginName
   
  -- Audit Server Principal Impersonation Event
  exec sp_trace_setevent @TraceID, 132, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 132, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 132, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 132, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 132, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 132, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 132, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 132, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 132, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 132, 64, @on  -- SessionLoginName
   
  -- Audit Database Principal Impersonation Event
  exec sp_trace_setevent @TraceID, 133, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 133, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 133, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 133, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 133, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 133, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 133, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 133, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 133, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 133, 64, @on  -- SessionLoginName
   
  -- Audit Server Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 134, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 134, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 134, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 134, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 134, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 134, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 134, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 134, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 134, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 135, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 135, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 135, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 135, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 135, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 135, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 135, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 135, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 135, 64, @on  -- SessionLoginName

  -- Audit Change Database Owner
  exec sp_trace_setevent @TraceID, 152, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 152, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 152, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 152, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 152, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 152, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 152, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 152, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 152, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 153, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 153, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 153, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 153, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 153, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 153, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 153, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 153, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 153, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 153, 64, @on  -- SessionLoginName
  
  -- Audit User Error Message Event (Ex)
  exec sp_trace_setevent @TraceID, 162, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 162, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 162, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 162, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 162, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 162, 64, @on  -- SessionLoginName

  -- Audit Server Scope GDR Event
  exec sp_trace_setevent @TraceID, 170, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 170, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 170, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 170, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 170, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 170, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 170, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 170, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 170, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 170, 64, @on  -- SessionLoginName
   
  -- Audit Server Object GDR Event
  exec sp_trace_setevent @TraceID, 171, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 171, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 171, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 171, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 171, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 171, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 171, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 171, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 171, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 171, 64, @on  -- SessionLoginName
   
  -- Audit Database Object GDR Event
  exec sp_trace_setevent @TraceID, 172, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 172, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 172, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 172, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 172, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 172, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 172, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 172, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 172, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 172, 64, @on  -- SessionLoginName
   
  -- Audit Server Operation Event
  exec sp_trace_setevent @TraceID, 173, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 173, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 173, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 173, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 173, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 173, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 173, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 173, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 173, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 173, 64, @on  -- SessionLoginName
   
  -- Audit Server Alter Trace Event
  exec sp_trace_setevent @TraceID, 175, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 175, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 175, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 175, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 175, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 175, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 175, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 175, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 175, 64, @on  -- SessionLoginName
   
  -- Audit Server Object Management Event
  exec sp_trace_setevent @TraceID, 176, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 176, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 176, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 176, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 176, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 176, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 176, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 176, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 176, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 176, 64, @on  -- SessionLoginName
   
  -- Audit Server Principal Management Event
  exec sp_trace_setevent @TraceID, 177, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 177, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 177, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 177, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 177, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 177, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 177, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 177, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 177, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 177, 64, @on  -- SessionLoginName
   
  -- Audit Database Operation Event
  exec sp_trace_setevent @TraceID, 178, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 178, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 178, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 178, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 178, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 178, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 178, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 178, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 178, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 178, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Access Event (Ex)
  exec sp_trace_setevent @TraceID, 180, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 180, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 180, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 180, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 180, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 180, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 180, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 180, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 180, 64, @on  -- SessionLoginName


  -- Set the trace status to start

  exec sp_trace_setstatus @TraceID, 1
  
  PRINT 'INFO: Successfully created the trace with ID ' + CAST (@traceid AS varchar(10))
  RAISERROR ('SQL Server 2008 started, CC trace process started.', 1, 1) WITH LOG

  PRINT 'Passed'	
  return (0)

PROC_ERROR:
  PRINT 'Failed'	
  return(1)