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
- tcpdump tcp port 1433 -i eth2 -s 0 -U -w /home/taarik/pass_retrieval1.cap -n
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:
- alert tcp any any → 145.100.105.32/27 (content: “|6D 00 61 00 73 00 74 00 65 00 72 00 2e 00 73 00 79 00 73 00 2E 00 73 00 71 00 6C 00|”; msg:“mssql authorization data retrieval!”;)
we forgot a port and a sid
- alert tcp any any → 145.100.105.32/27 1433 (content: “|6D 00 61 00 73 00 74 00 65 00 72 00 2e 00 73 00 79 00 73 00 2E 00 73 00 71 00 6C 00|”; msg:“mssql authorization data retrieval!”; sid:999999;)
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
- alert tcp any any → 145.100.105.32/27 1433 (content: “|70 00 61 00 73 00 73 00 77 00 6f 00 72 00 64|”; msg:“mssql table with password retrieval attempt!”; sid:999998;)
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.
- alert tcp any any → 145.100.105.32/27 1433 (content: “|61 00 63 00 63 00 6f 00 75 00 6e 00 74|”; msg:“mssql honeypot accessed!”; sid:999997;)
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()+"')
Test your check using e.g. the original client application and an ODBC link.
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.
