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.
spiral2007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.