April 1, 2008 at 6:01 am
In our organisation, we are not at all knowledgeable about Windows administration or Active Directory, so we tend to do things the 'safe' way by choosing mixed mode for authentication and then using ONLY the SQL Server 2000 login accounts to access our databases from VB6 applications.
We also configure client servers in the same way, since most of our clients do not have any other databases on SQL Server apart from ours, and they no nothing about administering SQL or Windows, it is normally not an issue.
I would like to know the pros and cons oof each authentication method and how Windows authentication can be implemented securely by someone who knows little about Windows administration.
The main reason is I need to implement logging triggers on each INSERT, UPDATE and DELETE statement but since this is a networked application, I have no way of passing to SQL Server the identity of the user or his workstation (although I am sure there are commands to achieve that too).
April 2, 2008 at 6:41 am
This is pretty basic, but here are some key issues, pros and cons.
Windows Authentication:
Cons - you don't have complete control over the accounts. I.e., a domain Sysadmin can create new logins. Also, your developers have to capture the user account and pass it to the database, rather than hard-coding security in their apps.
Pros - the con above is also a plus if you look at it like this - your DBA doesn't have to administer all those accounts. Also, your DBA gets to decide which accounts created in Windows have access to your data.
My preference is to assign roles and then add users to the roles. This works with either type of accounts. We make sparse use of SQL accounts and for user access only use Windows security.
The reality of it is that a SQL DBA needs to be savvy about Windows security. Likewise, it's good for your Windows SysAdmin to be cognisant of SQL security needs. Mixed authentication is my recommendation but focus on using your users' Windows accounts for their access.
There's a caveat to this approach however. If you know for a fact that Windows SysAdmin is lax about enforcing password rules and lets users share security, you might rethink allowing Windows user accounts. But this goes back to being savvy about your Windows environment.
Hope this isn't too simplistic to be useful.
April 2, 2008 at 7:48 am
Some of the advantages of using windows accounts:
- Windows accounts can force password changes on a regular basic
- You don't have imbed passwords in programs or force users to enter a password when starting up your application
If a user is a member of the domain where Sql Server resides, I use the domain accounts. I do have users that are not members of our domain that needs to have access to our DBs, so I have set up Sql Server accounts for them.
Steve
April 2, 2008 at 8:25 am
That's a big one for me... the use of "Trusted Accounts" means that I don't have to hard-code user names or passwords in any code. Very big security advantage for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2008 at 9:05 am
sblock (4/2/2008)
Some of the advantages of using windows accounts:- Windows accounts can force password changes on a regular basic
- You don't have imbed passwords in programs or force users to enter a password when starting up your application
If a user is a member of the domain where Sql Server resides, I use the domain accounts. I do have users that are not members of our domain that needs to have access to our DBs, so I have set up Sql Server accounts for them.
Steve
Check out http://www.cerias.purdue.edu/weblogs/spaf/general/post-30/ for an interesting discussion on the point of forcing regular password changes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2008 at 9:33 am
Jeff Moden (4/2/2008)
That's a big one for me... the use of "Trusted Accounts" means that I don't have to hard-code user names or passwords in any code. Very big security advantage for me.
That pretty much sums it up for me. And I don't have to type things in to create the SQL accounts, I'll just go browse/search through AD and pick the groups or specific users as necessary.
April 2, 2008 at 9:39 am
Thanks GSquared for the link to the password article. It makes you think. On the surface it is not apropos to the original post but if you look carefully, it is very relevant, as it counters some of the pros and cons of Windows security, such as it is.
So, the bigger question is: do we give our DBAs the responsibility for maintaining database security on their own or do we expect our Network SysAdmin types to share the load?
I go back to my earlier comment - DBAs need to be SysAdmin savvy and SysAdmins need a more thorough knowledge of database needs. Yes, we have to stretch ourselves and learn a little about each others jobs.
Michael Coles mentioned passwords in this article - http://www.sqlservercentral.com/articles/Miscellaneous/2744/. It behooves the DBA to get smart on security.
Adapt or perish (gosh, that sounds much harsher than is intended).
April 2, 2008 at 10:45 am
Hate to deviate, but apart from what's put down already, both Windows and SQL auth suffer from the problem that anyone can connect with any application and access the data. If you only have access to stored procedures, this might not be a big deal, but if you have tables, this could cause problems.
Application roles can help here, give very limited rights to logins and users, then give the rights to the app role and protect that password.
April 2, 2008 at 1:29 pm
Steve Jones - Editor (4/2/2008)
Hate to deviate, but apart from what's put down already, both Windows and SQL auth suffer from the problem that anyone can connect with any application and access the data. If you only have access to stored procedures, this might not be a big deal, but if you have tables, this could cause problems.Application roles can help here, give very limited rights to logins and users, then give the rights to the app role and protect that password.
Limiting roles for logins to minimum necessary privileges is definitely necessary, regardless of the type of login. Groups make this pretty easy to manage, but it has to be done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2008 at 2:25 pm
Steve Jones - Editor (4/2/2008)
Hate to deviate, but apart from what's put down already, both Windows and SQL auth suffer from the problem that anyone can connect with any application and access the data. If you only have access to stored procedures, this might not be a big deal, but if you have tables, this could cause problems.Application roles can help here, give very limited rights to logins and users, then give the rights to the app role and protect that password.
I agree with Steve... most folks do a GRANT ALL ON everything TO PUBLIC and that's definitely not the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2008 at 1:07 am
April 3, 2008 at 6:53 am
I am forced to admit that application roles still baffle me. Specifically, I have never figured out how to join tables that cross database boundaries using application roles.
For instance if I want to query:
select
col1,
...
colN
from database1.dbo.table1
join database2.dbo.table2
I don't understand how to get this query to work if I am using application roles; can you help with this?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply