Restrict access to Database
-
Hello,
Previously we used to store sql user logins in registry or xml for our applications(made in C#) to access the database. Now we are proposing that the application be logged on by the user with their windows loggin id(windows credentials) and in turn access the database. We do not want to store the loggin id and password in the database. Hence how do we implement the same.
Also we want that only the applications created by us can get access to the database.
For example
User A, B and C have access to application and in turn access to the database. but these users should not be able access the database say using excel or any other third party application.
Thanks for help
-
Since you're using 2008, you could create a logon trigger that checks which application they're trying to logon from. You'll need to take some care because you could end up preventing logons by anyone. Develop such a thing on a test server and test like crazy before you put it in prod.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
saum70 (12/27/2014)
Hello,Previously we used to store sql user logins in registry or xml for our applications(made in C#) to access the database. Now we are proposing that the application be logged on by the user with their windows loggin id(windows credentials) and in turn access the database. We do not want to store the loggin id and password in the database. Hence how do we implement the same.
Also we want that only the applications created by us can get access to the database.
For example
User A, B and C have access to application and in turn access to the database. but these users should not be able access the database say using excel or any other third party application.
Thanks for help
I've used a logon trigger to achieve this and it works very well.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Out of curiosity, any performance considerations with logon triggers that you are aware of?
----------------------------------------------------
-
MMartin1 (12/29/2014)
Out of curiosity, any performance considerations with logon triggers that you are aware of?Just like any other trigger, it depends on what you're doing with it. There's nothing inherently evil in them, no.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Be aware that anyone can spoof the application name in their connection string. In practice, it probably doesn't happen, but just be aware you have a hole.
-
Steve Jones - SSC Editor (1/2/2015)
Be aware that anyone can spoof the application name in their connection string. In practice, it probably doesn't happen, but just be aware you have a hole.Is there any way to close this hole?
We're currently discussing to go to Windows login, too.
But we didn't find a way to prevent access other than through the way we currently "allow".
At the moment the account a user logs into our application is different to the one created at the database layer (one can be converted into the other).
So this account cannot be used to open an ODBC connection. We also use the logon trigger together with an application name.
I'm not sure if the SQL login name as well as the application name we use can "easily be spoofed" if the connection is via https...
Going to Windows logins we'd loose one part of our "misuse guards".
-
i think it's application name and hostname(workstationID) that can be spoofed. username , not a login, bu if your priviledges were high enough you can start using execute as login='someotherSQLUser'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
No way I'm aware of. You can specify this in the connection string:
$cstr = "Server=WS12SQL;Database=Northwind;Integrated Security=SSPI;Application
Name=SetupNorthwind";
From Allen White's piece: http://sqlmag.com/scripting/using-adonet-connection-string
If anyone sets this programmatically, including from PoSh or VBScript, they should be able to spoof any app.
-
No, you can't keep someone from defining their app name in the conn string, but you can also limit it by hostname so it could be a combination of appname and hostname. So it has to be a certain app coming from a certain box.
If you really wanted to vary it, you could work with your app team to change the name they're putting into their string every couple wks or every month... whatever. Then just change it in your trigger too and you're golden.
The only thing your logon trigger should slow down would be if you had a lot of logic in there it could briefly slow down the connections, but once they're in it has no more effect.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
How would using a logon trigger compare with using application roles to do this?
-
crmitchell (1/12/2015)
How would using a logon trigger compare with using application roles to do this?App roles require the application to run a proc and enter a password. If the app is protected, then this can prevent access from Excel/etc if the user doesn't know the password. However, it's a bit of obfuscation. If the user decompiles the app, potentially they are going to learn the password.
Beyond most users and probably good enough, but you have to be able to implement this in your application code.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply