April 18, 2006 at 5:13 pm
i don't want people connecting to SQL server using other programs like EM or query analyzer except the application. is it possible to do that without using application roles? btw, i am not sure if using application role will help me there.
April 18, 2006 at 6:40 pm
There has to be a login available for the application to connect to the server, and it has to have access to a database, before it can use an application role. You can severely restrict the permissions for this login, but it has to be able to connect. Which means people can connect with Enterprise Manager or Query Analyzer and see the database structure.
You might have something run every 30 seconds that looks for non-application, non-administrator connections and kills them. There may be more elegant ways that I am not familiar with, you might search for information on SQL 2000 login auditing.
April 18, 2006 at 7:42 pm
but how can i differentiate the applications? i mean which one is EM, QA or custom application? i am using windows authentication. so they will be able to connect using any application if they have access to the database.
April 19, 2006 at 8:50 am
Look at the program_name field in the results of sp_who2. You will see "SQL Query Analyzer", "SQL Query Analyzer - Object Browser", "MS SQLEM", and/or "Microsoft SQL Server Management Studio" and "Microsoft SQL Server Management Studio - Query" for the connections from EM, QA, or SSMS.
You can try to tune a query to pull out the unwanted connections with something like:
select
spid, program_name, hostname, loginame, open_tran, cmd, login_time, last_batch
from master.dbo.sysprocesses
where spid > 50
and loginame <> 'sa'
and program_name not like 'SQLAgent%'
and hostname <> '<DBAs computer>'
and program_name in ('SQL Query Analyzer', 'SQL Query Analyzer - Object Browser', 'MS SQLEM',
'Microsoft SQL Server Management Studio', 'Microsoft SQL Server Management Studio - Query')
The first four conditions in the WHERE clause should exclude system processes, SQL Agent jobs, and admin connections. The last condition looks for specific programs. If you control the application code (or at least the connection strings), you can add "Application Name=MyApplication" to the connection strings and change the last condition to "and program_name <> 'MyApplication'". You might also want to worry about nefarious users who switch to something like Apex SQLEdit or Quest Toad for SQL Server to get around your watchdogs.
I would suggest adding "and open_tran = 0" and/or "cmd = 'AWAITING COMMAND'" to skip connections that are active.
If you have tested this extensively, and are positive you won't break anything, you could use "select 'KILL ' + cast(spid as varchar)" to make this query generate the commands to close these connections.
I don't know your reasons for wanting to do this, so I can't say that this is a good idea. I have not been in a situation that called for this, and all my users are withing easy reach if I need to scream or use violence, so I wouldn't do this myself. If you think it is a good idea, you just need to figure out how to execute the KILL commands.
One interesting aspect is that until they figure out what you did, you can just play innocent and tell them they all must have some kind of virus that's breaking their connections.
April 19, 2006 at 8:57 am
Another good condition for the WHERE clause would be "and hostname <> @@servername" to be extra careful not to kill a server process or scheduled job.
This assumes users can't just go over to the server and login, or use a Remote Desktop connection to the server.
April 19, 2006 at 7:24 pm
thanx scott.
i have created a job to run every 1 minute and execute that sp. is there any way i can make the recurring job frequency less than 1 minute? btw, i think its better to run the sp at the start of making a new connection to db instead of running it every 1 minute but i don't know how to do that. any ideas will be appreciated.
April 20, 2006 at 7:54 am
I've never tried this, but a quick search on MSDN for "SQL Server 2000 login auditing" turned up a lot of links, including this article that discusses enabling C2 auditing (probably overkill) and alternatively using a SQL Profiler trace to watch for login events.
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
It also shows how you can set up the server to automatically run this trace every time it is started. I'm not sure how you go from collecting the information in the trace to reacting to it and closing unwanted connections, but possibly you could have the login events sent to a trace table and put an insert trigger on the table to examine each event as it happens. I imagine you would filter the trace events using the conditions suggested previously for the sysprocesses query.
April 20, 2006 at 8:04 pm
as mentioned in the article from the link you provided, i can start trace to trace events using filters. but the problem with using sp for tracing is i couldn't find way of directly writing the trace into table.
http://support.microsoft.com/kb/270599
this article shows how to write trace into table but trace has to be stopped first. so i don't think it will help me as in my case, it has done as soon as the trace is captured so that it triggers a trigger which can close unwanted connections.
if i run a trace from SQL profiler, it can write directly into table. but in my case, this trace needs to run as long as the server is running.
April 21, 2006 at 7:55 am
Apparently you could set up a trace table in SQL Server 7.0 using xp_trace_addnewqueue, but not in SQL 2000 with sp_trace_create. I don't know how Profiler does it. Maybe you should run one instance of Profiler to capture all the commands from another instance to see how it is done? There may not be any undocumented magic going on, it may just be tracing to a hidden file and transferring data from the trace file to a trace table itself.
If you create a Profiler template for login tracing, it would be easy (but annoying) to manually start SQL profiler to run that trace every time you reboot. How big an annoyance that is depends on how often you reboot.
If you have a trace running to a trace file (started automatically whenever SQL Server is started), there is probably a way to write a program that would be notified whenever a new record is written. Or simply have a program that wakes up every few seconds and checks to see if the trace file size or modification date has changed.
If you turn on SQL login auditing so all logins events are recorded in event logs, you could write a program using NotifyChangeEventLog to react and run your login validation. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/eventlog/base/receiving_event_notification.asp
I found one suggestion that you can create an alert in SQL Server 2000 that can be triggered by a login event. If this is true then you simply have to create a login-validation job that runs your validation proc, and associate the job with the alert. I assume you would have to first turn on SQL login auditing, and then check to see what message numbers are being logged for these events. The reference I found was very short on details.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply