Authorization data (usernames and passwords):

Find out where the authorization data is stored within the database.

From the sql-injection-cheat-sheet-mssql we see that for getting a list of password hashes in MSSQL 2000 it used to be in master..sysxlogins, but in MSSQL2005 it is in master.sys.sql_logins

Write an SQL query that dumps all relevant authorization data over the network. Do not use the client package that is installed in the VM (data will not be sniffed by your IDS).

SELECT name, password_hash FROM master.sys.sql_logins

By using this sql command we can find all the SQL users. And there are windows users who are also authorised to use the database, but that information is not stored here.

Is this action detected by Snort?

no

If not: write a Snort rule yourself.

we are going to repeat the retrieval, only this time we are going to do a tcpdump on port 1433

using the explanation from packetstormsecurity we can make own rules and catch the “m.a.s.t.e.r…s.y.s…s.q.l” content with the binary retrieved from the tcpdump like so:

we forgot a port and a sid

Doing the sql query again we see now that Snort has detected this

Intrusion Detection:

Write an SQL query / stored procedure that looks for a specific column name in a given database and execute it (e.g. look for columns with the name password).

We want to find the column with “password” in its name. To do this, we join all the tables with all columns based on the object id. 

SELECT allt.name AS tname, col.name AS cname
FROM sys.all_objects AS allt
LEFT OUTER JOIN sys.all_columns col ON allt.object_id = col.object_id 
WHERE col.name LIKE '%password%'

Is this action detected by Snort?

No. It does not hit any rule predefined nor the one created by us.

If not: write a Snort rule yourself.

We are going to do the same thing as before, only now catch the signature for “p.a.s.s.w.o.r.d”. Looking in the tcpdump we see

the binary is 70 00 61 00 73 00 73 00 77 00 6f 00 72 00 64 so the snort rule becomes

Setup a table in the database that seems to contains fake sensitive information (honey pot table), e.g. credit card and bank account information.

we already added a database called “account” in assignment 1, it contains table names like salary and account. Doing a “use account”, we got the following binary string 

00000070  00 00 00 00 00 00 00 00  01 00 00 00 75 00 73 00  |............u.s.|
00000080  65 00 20 00 61 00 63 00  63 00 6f 00 75 00 6e 00  |e. .a.c.c.o.u.n.|
00000090  74 00 bc a8 cd 4b f7 a6  0d 00 fd 00 00 00 fd 00  |t....K..........|

extracting account and putting it in a rule gives is in the next section

Create an alarm mechanisms that alerts an admin if the honey table is accessed.

result is an alert snort like before.

Intrusion Prevention:

Active database sessions store the characteristics of the application that is uses to setup the session. Find out where and how the application name of the client application is recorded in the database.

http://www.cryer.co.uk/brian/sqlserver/howtolistallconnectedusers.htm
We can deduct from the above page the SQL sentence we need 

select program_name from master..sysprocesses

Write database code that checks the application name:

Create a list of trusted application names.

We created a table called “trustedapplication”. And added only the original client appllication “Microsoft SQL Server Management Studio Express” And the query tool. If we want to accept other clients , we can simply add them to the table later.

Looking at the next sources have-sql-demand-application.htmlUsing-Triggers-In-MS-SQL-Server we tried to make a logon trigger.

Right credentials using a trusted application? Access granted.

We need to setup the “logon trigger”. Which will use the stored procedure to check program_name and spid from master..sysprocesses if the client name is in our trusted application table. 

CREATE TRIGGER Deny_Generic_ConnString_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @AppName nvarchar(128)
SELECT @AppName = [program_name]
FROM master..sysprocesses
WHERE session_id = EVENTDATA().value(
    '(/EVENT_INSTANCE/SPID)[1]', 'int')
END
Right credentials using an untrusted application? Access denied and generate an alert.

If not, the spid will be killed. Hence the user is killed from the database. And the event will be logged into another database “alert_app” for security purpose. The next code is inserted before the end of the previous code. 

IF @AppName NOT IN ('trustedapplication')
    KILL session_id;
    insert into alert_app (appname,date) values('@Appname', '"+DateTime.Now.ToLongTimeString()+"')

testing the connection using both original client and the asp code. But this did not work, because apparently logon trigger is not supported with this “free” version of “MSSQL Express Edition” we are working with

<hi #c0c0c0>improvement</hi>
Link to MSDN about logon trigger
logon trigger is event driven. LOGON is not a valid event in <hi #ffff00>Express Edition</hi>. Reference from this website and may be more clearly from this website
Run following code will give you an specific error which tells you that LOGON is not a valid event type. 

CREATE TRIGGER welcome ON ALL SERVER
AFTER LOGON
AS
  PRINT 'Welcome!';
GO
 
Msg 1084, Level 15, State 1, PROCEDURE welcome, Line 2
'LOGON' IS an invalid event TYPE.