Auditing in SQL Server

Identify TCP Port Number of a SQL Instance

There are a lot of instances when you are asked to identify TCP port number for a SQL instance unless you use default port (1433) across the board. There are several ways to achieve this and as a DBA you should always be looking at ways to get work done accurately with least possible effort.

---

Identify Port Number via SQL Server Error Logs

As per BOL “SQL Server error logs contains user-defined events and certain system events”. To me, SQL Server error logs, this holds great information on what is happening with your db server. Along with plethora of information, it also holds information about the tcp port number of the instance. A simple tsql will fetch the required information without you rdp’in to the server.

1	exec master..xp_readerrorlog 0,1,'Server is listening on'

Notes:

• If your environment uses sp_cycle_errorlog then the above query will yield “(0 row(s) affected)”. In this case you will need to search the archive logs. Logging of port number is only in startup error logs.
• If your environment uses SQL Database Mirroring you will see those ports as well. To avoid the confusion look under column ProcessInfo where value = ‘Server’.

---

Identify Port Number via DMV – sys.dm_exec_connections

There is an excellent article by Manohar Punna providing detailed insight onsys.dm_exec_connections. Again, this is an excellent way to fetch the port number without rdp’in to the server.

1 2	SELECT local_tcp_port as [SQLServer_PortNumber] FROM sys.dm_exec_connections WHERE session_id = @@spid --this is your connection spid

with the same end result:  

---

Identify Port Number via  SQL Server Configuration Manager

This step will require you to rdp/connect to the server. Connecting to SQL Server Configuration Manager can be achieved  in 2 ways

1. Using run command (Start –> run or ) and type - sqlservermanagerXX.msc (XX -10 for 08/R2; 11 for 12)
2. Go to Start–>All Programs–> Microsoft SQL Server xxx–>Configuration Tools–>SQL Server Configuration Manager
   • Under SQL Server Configuration Manager–>SQL Server Network Configuration–>Select Protocols for <select instance>
   • Goto Properties for TCP/IP–>IP Addresses(tab)–>Scroll all the way to IPAll–>TCP Port

---

Identify Port Number via Windows Event Viewer

This step will require you to rdp/connect to the server. You can connect to Event Viewer

Go to Start–>All Programs–>Administrative Tools–> Event Viewer

1. In there click on Windows Logs–>Application and find via either ways
   • EventId – 26022
   • Description - Server is listening on [ ‘any’

Notes:

• If you are searching via EventId and if your instance has db mirroring enabled you will have to cycle till you see the description  “Server is listening on [ ‘any’“

There are additional methods floating around (powershell, xp_instance_regread) but my main focus – as a DBA – is to keep it simple without doing to many additional tasks.

Server Level Audit using TSQL

Hi Friends,

In my previous blog I describe about auditing and auditing setup using management studio. You can go to that blog by using this link;

Today here we will setup the server level audit using TSQL:

Step 1: Create Server Audit

1 2 3 4 5 6 7 8

Use master go CREATE SERVER AUDIT AUDITDEMO TO File (FilePath='E:\AuditLogs\'     , MAXSIZE = 1024 MB         , MAX_ROLLOVER_FILES = 5       , RESERVE_DISK_SPACE = ON) WITH (QUEUE_DELAY=1000, ON_FAILURE=CONTINUE)

Note: Here disk space will be reserve (after enabling the AUDITDEMO).

Step 2: Create Server Audit Specification

1 2 3 4 5

Use master Go CREATE SERVER AUDIT SPECIFICATION SERVERAUDITSPECDEMO FOR SERVER AUDIT AUDITDEMO ADD (FAILED_LOGIN_GROUP)

Step 3: Enable both

1 2 3 4 5 6 7

Use master Go ALTER SERVER AUDIT AUDITDEMO WITH (STATE=ON) GO ALTER SERVER AUDIT SPECIFICATION SERVERAUDITSPECDEMO WITH (STATE=ON)

Step 4: You can also check that both options are on or not by using below DMVs. If both query return value 1 that means both are enabled

1 2 3 4

Use master GO Select is_state_enabled from sys.server_file_audits Select is_state_enabled from sys.server_audit_specifications

Step 5: JUST DO A TRIAL OF FAILED LOGIN using SSMS (Here I just try to login with a login name DSSS, which not exist on server)

Step 6: Now you can read your Audit log file by using

1	select event_time,server_principal_name,statement from fn_get_audit_file('E:\AuditLogs\*',null, null)

Output of above code is as shown below:

HAPPY LEARNING!

Server Level Audit using Management Studio

Hi Friends,

When we are using SQL Server in our organization then most of the time we required Auditing. Microsoft provide Auditing feature from SQL Server 2008 onwards but this is an Enterprise feature. There are two types of Auditing which you can implement, first is Server level audit second is database level auditing. Before SQL Server 2008, if you want to use Auditing then we implemented this by using various things like SQL Trace, DDL Triggers, login auditing. There are so many drawbacks of using all these things in comparison of SQL Server Audit feature: like performance, less granularity etc. By using SQL Server Auditing feature you can achieve more security, good performance and easy management. Here i will show you how can you implement Server Level Auditing using Management Studio (Here I am using SQL Server 2012).

Open SSMS and connect SQL Server Instance, then go for audit under security inside object explorer. Now right click on Audit option then click on New Audit..

This will open up a new window, where you can fill up:

1- Name of audit

2- Mode of auditing, synchronous (QUEUE_Delay=0) or asynchronous (QUEUE_DELAY=1000)

3- Action to be taken if logging failed (Continue, Shut down Server, Fail Operation)

4- Audit destination (File, Application Log, and Security Log)

Note: here if you will use file option, then you can configure more options to manage those files like file path, maximum files, maximum file size etc.

Here I am choosing application log.

Now we will create server audit specification as shown below:

This will open up a new window where you can fill up:

1- Name of server audit specification

2- Name of server audit (which we created earlier)

3- Action which you wants to audit as shown below

Here for testing purpose i am taking Failed_Login_Group, and then click OK.

Now enable both server audit and server audit specification, by right click then click on enable option for both.

Here Setup has been completed. Now you can again connect the same instance from SSMS but with a wrong username and password. This will fire a failed login. As per our Audit this failed login entry will go in Application Log. You can check this entry by opening your server Application Log.

HAPPY LEARNING!