December 7, 2011 at 11:25 am
Hello Everyone,
My new job has me more involved in SQL server (come from an Oracle shop) and as I dive in, questions keep coming up that I would like to pose here in order to get a starting security framework for new app development moving forward.
From searching and reading on this forum (awesome resource by the way) I think I now have a good understanding of the difference between SQL accounts and integrated windows security. Sorry the the long winded post but my mind is too full of questions 🙂
1. When setting up a new SQL instance it seems that going with windows integrated is the best practice, only using SQL logins if absolutely required for legacy apps, is this true?
2. When a domain user gets a SQL login, and then a user account with permissions on a given database what if anything is stopping them from accessing the SQL instance from Excel or MSQuery or even the management studio if they were to install it? If I write a new application I want to make sure that database transactions can only be performed through the app. If a user can delete records through an app could they not delete all records in a table with management studio access?
3. From a .NET perspective I will be using trusted connection strings (for a new app) so that no passwords are sent over the network. My understanding is that the cached user credentials on the client machine will be passed to SQL server. Does this not mean that anyone could start this app on someone elses machine and they would in effect gain access as that user?
My head is spinning, more questions to come.......
December 7, 2011 at 11:47 am
1. Yes, integrated is better. Many leave SQL Login enabled as a backup as well.
2. You could try creating a database user for your application without login
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2011 at 11:56 am
for #3, a Windows machine that is logged in will pass the token related to the user to the SQL server.
the token has the built in assumption that if i was ok when i logged in, I'm still "OK" right now..no need to go back to the domain controller and ask "did you revoke access yet?" for every network resource you touch. that's what the cached credentials issue is for what you are asking. your password might expire, but you are still OK until you log in again, for example.
If that user logs out/reboots, the token is lost, so noone else could sneakily use their credentials.
If someone logs in on the same machine, it's THEIR security token that is passed, not someone else on the machine.
so the only (relevant) way to use someone elses credentials is if they logged in, and walked away from their desk without locking their workstation.
if I know your username and password on the domain, i could login as you from my own machine, of course, but that's not exactly what you are asking.
when the .NET app is run, it's run under the token of the person that is logged in/running/clicking the app.
it is possible to use impersonation, but that's done by code in an application, and not really applicable here, i think.
Lowell
December 7, 2011 at 12:44 pm
SQLRNNR (12/7/2011)
1. Yes, integrated is better. Many leave SQL Login enabled as a backup as well.2. You could try creating a database user for your application without login
Hi Jason,
I though both a login and a user were required for access?
Tim.
December 7, 2011 at 1:05 pm
1. Integrated security is probably the best but in some cases where the web server or a middleware piece are not in the same or a trusted domain this cannot be used. So while it is desireable to use only trusted authentication it is not often as feasible as you would think.
2. Yes they could, which is why it is common for especially web apps to login with their own login and manage their own security rights. This does not require that the individual users be known to SQL just that the applications login is AND that the application is aware of the user.
3. Lowell answered this very clearly.
I'm not a fan of granting SQL Server level access to individual users (or preferably even groups of users). I'd rather the application handle their rights. This cuts down on the number of user accounts (or AD Groups) that I have to worry about in my SQL Server.
BTW Lowell, updated build last night..
CEWII
December 7, 2011 at 2:16 pm
Elliott Whitlow (12/7/2011)
1. Integrated security is probably the best but in some cases where the web server or a middleware piece are not in the same or a trusted domain this cannot be used. So while it is desireable to use only trusted authentication it is not often as feasible as you would think.2. Yes they could, which is why it is common for especially web apps to login with their own login and manage their own security rights. This does not require that the individual users be known to SQL just that the applications login is AND that the application is aware of the user.
3. Lowell answered this very clearly.
I'm not a fan of granting SQL Server level access to individual users (or preferably even groups of users). I'd rather the application handle their rights. This cuts down on the number of user accounts (or AD Groups) that I have to worry about in my SQL Server.
BTW Lowell, updated build last night..
CEWII
Thanks for the reply Elliott,
When you say your not a fan of granting server level access does this mean if given the choice you would opt for a single SQL login? Everything I read says to avoid them and only use them if you have to?
December 7, 2011 at 2:51 pm
hanrahan_tim (12/7/2011)
Thanks for the reply Elliott,When you say your not a fan of granting server level access does this mean if given the choice you would opt for a single SQL login? Everything I read says to avoid them and only use them if you have to?
Like many thinks in SQL It Depends..
Limiting the number of Users who have direct access to SQL reduces the attack surface available. But increases the need for a strong password on the accounts that DO have access. What you lose is the ability to have SQL say user X login in from IP address at 4:30PM. However the application is capable of capturing this same data it changes where it happens. What SQL sees is AppUser made X query, with connection pooling in place there might not even have been a new login from the app level. If your application can be limited to an app pool all by itself then you can use the AD account to grant access to the database, but what often happens is that the IIS server and the web server are in different domains and different security zones and are not in the same domain. And a domain in a DMZ is never trusted on the internal network, the opposite is often true where the DMZ domain will trust the internal domain..
I would NOT add individual AD accounts to SQL in all but rare cases.. What I mean by that is that if your app has the user login as themselves I would add an AD Group for the application and require that the users who need access to it are members of the AD Group. I would then add the AD group and grant it as little rights at the server and database level as possible to run the app. Under absolutely ZERO, ZILCH, NADA cases would I give that AD group db_owner for the database. Your application still needs to manage internal security but the authentication piece would be handled by AD and a small portion of the authorization piece would be handled by SQL.
I have seen it done both ways, for interally developed software it is usually easier to make happen because you have more control. But my advice is to always shoot for trusted authentication but understand and accept that it will not always be possible OR make sense.
CEWII
December 9, 2011 at 9:05 am
hanrahan_tim (12/7/2011)
1. When setting up a new SQL instance it seems that going with windows integrated is the best practice, only using SQL logins if absolutely required for legacy apps, is this true?
Yes, Windows integrated only is preferred. Changing it to both is a simple setting switch with a restart of SQL Server being required. And it's not just legacy apps. For instance, the newest version of Hyperion requires SQL Server-based logins. That's just one example that comes to mind. 🙂 So even though you want Windows only, you may not be able to stick with that.
2. When a domain user gets a SQL login, and then a user account with permissions on a given database what if anything is stopping them from accessing the SQL instance from Excel or MSQuery or even the management studio if they were to install it? If I write a new application I want to make sure that database transactions can only be performed through the app. If a user can delete records through an app could they not delete all records in a table with management studio access?
If the user connects through the application with his or her Windows login, then yes, the user will have the same permissions through any other client. There are ways of architecting apps where the user has to go through a web service, etc., and if that's the case, the web service runs under its own user context. Therefore, the end user doesn't have direct access to the database.
3. From a .NET perspective I will be using trusted connection strings (for a new app) so that no passwords are sent over the network. My understanding is that the cached user credentials on the client machine will be passed to SQL server. Does this not mean that anyone could start this app on someone elses machine and they would in effect gain access as that user?
Trusted means that the security credentials used to start the app will be passed. So if it's "your" machine but I can log on with my user account, then it's my user account's credentials. Now, if you leave your computer unlocked after you've logged in and I double-click and start the app, then it is your credentials. But this isn't a database security issue. This is a security awareness problem. You have the same problem with any app, like Outlook. If a user doesn't lock his workstation, what's to stop you from sending an email "on the user's behalf?" Exactly the same issue.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply