The Case for SQL Logins - Part 1
-
We use SQL Logins primarily and this gives us the problem of not knowing the Win NT user when there is a user creating problem in the database.
-
I agree with you on this topic. I have been using a single sql login with the userid's managed by the application for years (since 4.5).
It was the most logical fit, especially since the domain admins are in a separate working group. For remote support of an application, it allows much more functionality and you can have a single point of support instead of "passing the buck".
Joe Johnson
NETDIO,LLC. -
I read the article again, and i still strongly disagree with you. No user whatsoever should have access to a database. So the problem of a user executing a stored proc will not arise. Only the SA should have access in a database. This is the only safe way.
-
I think you misposted. Users need and should have access to a database. Otherwise, what is the point. Not all apps can use stored procedures. Even if they do, a user needs access.
I would say that they should not have DBO access and carry it further to say that very, very few people should have dbo or sa access to a server.
Steve Jones
-
Good article. I'd like to read more.
-
Andy's arguments for using SQL accounts applies to *applications*... but what about controlling administrator access? In my organization, we have different levels of people who are in different server roles (e.g., full sysadmins vs. dbcreators). While we could have separate logins for each server role, it's easier to manage them using Windows accounts. Plus, if a DBA leaves, there are no password changes to worry about.
-
I like having both types of authentication. While I agree with access from the web SQL logins are best. I think the Windows Login allows for some useful functionality with tools etc. You can code without the use of a login which allows tools that are run against SQL to only be used by domain admins. The tools can be accessible but having the tool does not automatically gain access to the server.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD -
We control WA with a resource domain that only SQL servers are a part of with one way trust from the production domain. combine this with application roles and we feel confortable here. Well, as comfortable as you can with security...
-
AllyAnne,
NT Auth is nice in that the DBA group can be managed as it applies to the entire group. There are no password changes here either. If you use SQL Auth and a DBA leaves, without a doubt you should have a password change or an account deletion.
Unless I am misreading.
Steve Jones
-
Steve,that's my point--NT Authentication would be preferable for DBAs. (Sorry that was not clear.)
-
Ahhh, then I agree ()
.I'm usually for SQL Logins, they are easier to deal with and it's easy for NT permissions to get out of hand in any size org. That being said, they do provide extra effort and for System Admin tasks, I think they are great.
Steve Jones
-
Different strokes for different folks! I believe how you will do it will depend on your specific environment. Sometimes it's better to have WA, sometimes it's not. I believe this is why Microsoft has introduced "application roles".
- Ricky Artigas
-
I agree with you. Windows Authentication is not always the way to go. We have ORACLE and SQL Server databases. These databases pass data back and forth to each other. The only way to do this is with SQL Logins, not WA.
Ann L. Barron
Database Administrator
HomeSide Lending, Inc.
(904) 281-3471
Ann L. Barron
SQL Server Database Administrator
Washington Mutual Bank, FA
ann.barron@wamu.net
(904) 281-3471 -
Hey Andy, what will people win for reviewing one of my articles?? ;))
Some interesting points throughout the article. I run a range of sites where we utilise 3 db logins to a 300 table schema, each login basic segregates the model into into "subject areas" which are accessed via COM's at the business layer and are shared by 000's of end users who authenticate via active-dir. This model is very easy to administer, and the actual "user privs" to the applications screen components are managed "in-code" using a variety of tables. Auditing is throughout via triggers.
The only issue to watch out for is hiding the connectivity info, a text file INI is simply bad practice. I know of a few sites where hackers have gained entry to the server as a lowly user but gain access quickly to the DB as the INI has all the passwords to get in!
What I will say though, is that I do like to use win authentication for sysadmin access and rarely utilise the SA account.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Just have to be creative!
I agree about the ini file. Not sure I have a perfect solution, but I think I can offer something a little sturdier in the follow up article.
Andy
.Viewing 15 posts - 16 through 30 (of 75 total)
You must be logged in to reply to this topic. Login to reply