• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

How to Prohibit SQL MANAGEMENT STUDIO OR ANY OTHER PROGRAM accessing SQL Server 2008 R2 if we have Windows Authentication

We are running a Client/Server Application that is accessing the database using  the user windows authentication credentials. How can we prohibit all other programs accessing the database (e.g. SQL MANAGEMENT STUDIO OR ANY OTHER PROGRAM ) from the user's desktop? There are some secutiry concerns that the user might access the whole SQL Server instance or database from other tools, even if we have windows authentication.

Lookinf forward to your response.
0
spiral2007
Asked:
spiral2007
  • 7
  • 5
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
There are some secutiry concerns that the user might access the whole SQL Server instance or database from other tools, even if we have windows authentication.
If you give to the users the strictly permissions needed they can't do nothing no matter tool they use. If you are concern about this is because you gave more permissions that they should have.

Anyway, you can do it at least in two ways:

1.

Create an application role where you give permissions only for the desired application

2.

Create a logon trigger for check which program is connecting to SQL Server and deny the access if isn't the program you want.
Example of a logon trigger:
CREATE TRIGGER logonTrigger
ON ALL SERVER 
FOR LOGON
AS
BEGIN
    IF EXISTS (SELECT *
                     FROM sys.dm_exec_sessions
                      WHERE session_id = @@SPID
                          AND program_name NOT IN (N'Allowed App1', N'Allowed App2', N'Allowed App3')
        ROLLBACK
END

Open in new window

0
 
FarWestCommented:
Thanks Vitor
this is a very useful answer for me,
but after that can the Administrator change this trigger from Management Studio?

or it should exclude admin user or build an application to do basic management including changing this trigger in just in case and have it within application list?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can add a filter for administrators:
CREATE TRIGGER logonTrigger
ON ALL SERVER 
FOR LOGON
AS
BEGIN
    IF EXISTS (SELECT *
                 FROM sys.dm_exec_sessions
                 WHERE session_id = @@SPID
                      AND login_name NOT IN (N'sa', N'Admin1', N'Admin2')
                      AND program_name NOT IN (N'Allowed App1', N'Allowed App2', N'Allowed App3')
        ROLLBACK
END

Open in new window

0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
FarWestCommented:
thanks, and if he did not want even the administrator to logon, then he can can keep the application list in a table and modify it by a strict operation within his application while keeping this application name static in trigger query
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can do that but an administrator can always change the trigger even without the SQL Server Management Studio.
Also, I can't see the purpose to deny this kind of access to a DBA.
0
 
FarWestCommented:
Yes, you can do that but an administrator can always change the trigger even without the SQL Server Management Studio.
but that program should be included in the list right?

one of the  problems I can see in all database engines that they don't differentiate between administrator and database owner, administrator can make maintenance operations and backup .. etc on database, but this doesn't mean he has right on the data to do whatever he wish, you can hire new employee as administrator, should you trust him on your data?!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As a DBA I needed to sign a contract with a confidential agreement clause and this is the way the companies need to protect their intellectual property rights.
0
 
spiral2007Author Commented:
Thank you Vitor for your answer.
From where the dm_exec_sessions table gets the program_name. Does it come from the executable name of the application??? I would like to change that so that it can not be recognized easily from anyone.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Each connection gives the possibility to set the program name (Application Name=ProgramName) but since isn't required you'll find often that field with empty value.
0
 
spiral2007Author Commented:
So if someone find out what is the application name that is allowed to login with success they can easily change the application name of excel for example and login to the db through excel.
0
 
FarWestCommented:
@spiral2007 you start hacking the solution even before implemented, LOL
I think application name is different than Assembly Name inside executable,  but you can try it
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, I don't think they can do that with those kind of applications.
They can only do that with an application that they have the source code and where they can edit the connection string.
0
 
spiral2007Author Commented:
So let's say if I change the following connection string image002.png within excel is going to work or the program name in the session table in this case is going to be excel (based on the assemply code) ???
0
 
FarWestCommented:
I really love that you try it and tell me what will happened. (just make the select without any triggers)
but frankly speaking I now think it will respect the connection string value
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try but I'm expecting that you'll see 'ISpiralName' in the ApplicationName field. If you don't provide an application name, Excel should be the default one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now