February 8, 2010 at 3:37 pm
I'm wondering: what strategies are employed out there for preventing users (whose logins have access to a database through an application) from accessing the database through SQL Client tools, such as SSMS?
Suppose a user, domain/user1, is granted access to a production database through a user application, app1.
How can this user be prevented from ad-hoc access to the production database through SSMS?
Logon triggers sounds like a reasonable option, although I am not sure of the performance overhead.
Any other ideas?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 9, 2010 at 4:49 am
Using an "application role" may be helpful in this case.
Alternatively, make your application access the database through a user which has no other rights than the "EXEC" right on the necessary stored procedures.
Additionally, you can talk to the Windows admin about not installing the SQL client tools / preventing the installation of the SQL client tools on the workstations.
February 10, 2010 at 10:46 am
1) Create a server side trigger to monitor the login for this user account and check which machine they use this log in ID to come to the server. If I am not wrong you can also know which application they are using. If the user is not from the app server or from the desired application, then you can just kill the PID and send out an email to that user and his authorities and of course one for your self as well. Talk about this action with him and his team and don't let him do it.
2) Periodically change the password and update the web servers
3) If the web application showing the password some where, try to encrypt it.
4) You could set up the ODBC settings by yourself so no one will know the password.
February 10, 2010 at 11:23 am
Thank you for the answers.
The situation is like this: we have given access to the Windows accounts of members of our developer and business-analyst teams so they can provide application support and fix bugs when they occur. Connectivity of these accounts to the production database through the application is therefore needed.
However, we would like to prevent them from connecting ad-hoc to production through SQL Management Studio or other SQL client tools, such as sqlcmd.
Application roles is one option, but I will need to familiarize myself more with it and better understand the impact of such a change to our applications.
Resolving it on a user-by-user basis through talking to them and cautioning them against connecting ad-hoc to production is another option, although it could become untenable in the long run due to the number of such connections.
I find logon triggers an attractive option because it will not require a change at the application level.
However, I am not sure about the impact on application performance from having triggers firing with every user connection.
If anyone has experience with logon triggers that they would like to share, it would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 10, 2010 at 11:53 am
February 10, 2010 at 12:01 pm
sihaab (2/10/2010)
This will help you to understand what is server wide log in audit..Link 1)
Linke 2)
You can control the user by host name, application and more...
I Google my self on the web by typing "sql 2005 audit login"
Hope it helps
Thank you for the link, this is indeed about using logon triggers to control access.
Have you had any performance-related problems with this approach?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 10, 2010 at 12:13 pm
Not at all...It is just a trigger and fires every time when user logs in.
Believe me, if you are Operational DBA or Production DBA, you need this to track who comes to your box, you will need that information some time.
I run a report daily and send my self an email using SQL Mail in HTML format to my self to watch my servers.
One time I made a TSQL error in the Trigger, that prevented anyone to log in to the server. So be careful on that, I would test this code first at any development box first before deploy in the production. Keep a clear check list.
If you get in to issue use the following link to resolve it ...
--- This is what I did for my trigger and learned good lesson as well.
Go to the server using RDP
Run SQLCMD for dedicated admin access
c:> sqlcmd -S localhost -U sa -P password -A
>DISABLE TRIGGER MyServerwideTrigger ON ALL SERVER
>GO
>quit
February 10, 2010 at 1:11 pm
sihaab (2/10/2010)
Not at all...It is just a trigger and fires every time when user logs in.Believe me, if you are Operational DBA or Production DBA, you need this to track who comes to your box, you will need that information some time.
I run a report daily and send my self an email using SQL Mail in HTML format to my self to watch my servers.
One time I made a TSQL error in the Trigger, that prevented anyone to log in to the server. So be careful on that, I would test this code first at any development box first before deploy in the production. Keep a clear check list.
If you get in to issue use the following link to resolve it ...
--- This is what I did for my trigger and learned good lesson as well.
Go to the server using RDP
Run SQLCMD for dedicated admin access
c:> sqlcmd -S localhost -U sa -P password -A
>DISABLE TRIGGER MyServerwideTrigger ON ALL SERVER
>GO
>quit
Thank you for the valuable info!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply