Install SSCA
 1. Save the SqlConnAuditGUI.exe, ConnEngine.dll, and ssca.txt to the same folder
 2. Edit ssca.txt to include:
 • the names of your servers
 • the fully qualified names of the tables you query to test your audit solution
 • the names of any SQL logins you plan on using
 3. Use UAC to run SqlConnAuditGUI.exe as administrator, and as the Windows user required for auditing.

Using SSCA - Overview
This is how you run a series of test queries against one or more servers using different conneciton parameters for each test.
1. Highlight one or more servers to test. 
2. Select a single table. 
3. Select one or more connection types to make to each server you have selected. 
4. Select the batch level options you want, these will apply to each test connection. 
5. Click the Audit button.
6. SSCA will connect to each highlighted server once for each highlighted connection type, and run a query.
7. The basic format of each query is: 

select top 1 '[saltPrefix][saltSuffix]' as from [selectedTableName]. 

  Here is a sample of a set of queries that might be run on a single server:

select top 1 'Salt001' as Salt from master.sys.assemblies
select top 1 'Salt002' as Salt from master.sys.assemblies
select top 1 'Salt003' as Salt from master.sys.assemblies

 6. Logs are saved to file when the SCCA closes.

The Important Part - Finding the Audit Gaps
Each connection has a unique SaltPrefix+SaltSuffix  value in the query.  Look for all of the salt values in the DB audit trail.  Was the audit solution able to garner the username and other information for that salt value?  If it does not then your solution has an audit gap.

Test Batch SQL results sample

Test Batch Thursday, October 11, 2012 11:57:44 AM
Data Source=np:myServer;workstation id=SQLConnAudit;Application Name=SCCA_audit;Integrated Security=SSPI;
select top 1 'Salt003' as Salt from master.sys.assemblies
SALT                          Salt003

Audit Record Log Sample

The meanings of each of value in a line are given below:
•column 1 - indicates if the query was run succesfully.
  0 = successful connection and query
  2 = Network or instance-specific failure
  3 = Cannot generate SSPI context (Kerberos problem)
  4 = protocol not available, remote connections not allowed
  5 = the user does not have permission to perform this action
  6 = login failed
  7 = connection successful, but an error occurred during the login process.
•column 2 - the salt value that matches the SQL results
•column 3 - the protocol - named pipes, tcp, etc
•column 4 - the authentication method
•column 5 - date and time of the query
•column 6 - server name
•column 7 - name of the table that was queried

--Sample Audit Log--
0, Salt001, np, KER, 10/11/2012 11:54 AM, myServer, master.sys.assemblies
0, Salt002, np, KER, 10/11/2012 11:55 AM, myServer, master.sys.assemblies
0, Salt003, tcp, KER, 10/11/2012 11:57 AM, myServer, master.sys.assemblies
0, Salt004, tcp, KER, 10/11/2012 11:57 AM, myServer, master.sys.assemblies
0, Salt005, ., KER, 10/11/2012 11:57 AM, myServer, master.sys.assemblies
0, Salt006, ., KER, 10/11/2012 11:57 AM, myServer, master.sys.assemblies

Database Servers
Highlight one or more servers to test using the mouse and the control button.  Lines starting with "--" are section headers; no tests will be attempted on them.


Test Tables
Select a single table from the list.  The table should be found on each database server you have selected.

Batch Level Settings
Anything set here applies to all tests.
• App Name: Maps to applicationName property. (
• Workstation Id:  Maps to workstationId property. (
• SQL Login: The name of the SQL login to use for the test.  Ignored if the test uses Windows authorization.  The password is entered below the SQL Login, and is also ignored when the test uses Windows authorization.
• Show button: Reveals the SQL Login password.


•setup a Windows login specifically for auditing
•setup a SQL login specifically for auditing
•"GRANT VIEW SERVER STATE TO [MyAuditLogin]" to the accounts. This will make it easier to audit because you will then have access to tables that are found on all SQL servers. In addition you will be able to look at the connection and sessions on that server.
•Edit ssca.txt to include the tables that exist on all SQL servers (master.sys.assemblies, master.sys.certificates...)
•Changing the salt values and the names of the tables you are using for the test queries will generate an audit trail that is easy to identify and trace back to your audit solution.

Last edited Oct 16, 2012 at 5:04 PM by Hill5Air, version 25


No comments yet.