August 1, 2005 at 8:11 am
Hi,
I was under the impression that Windows Authentication is the best way of authenticating users to SQL Server. I was reading a book over the weekend about SQL Server Security and in that, the author was mentioning that SQL authentication will work just fine for a small-mid size company. Is this true, which type of authentication doesn't matter?
Thanks,
R
August 1, 2005 at 8:50 am
SQL authentication requires more user admin than Windows authentication - but in a mid-sized company that might not matter so much, as it's not that much more when there are not many users.
Many applications still use SQL authentication only, so you might be forced to use it - at least in mixed mode.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 1, 2005 at 9:07 am
There are pros and cons of both. If you use Windows, passwords are managed for you, users don't need a separate passworrds. However, I've seen some screwy domain issues, can't work across the Internet sometimes, etc.
SQLAuth works great across the Internet, WANs, etc. Separate password, etc. I've tended to stick with NT auth as it's one less thing for the DBA to manage.
August 2, 2005 at 1:46 am
August 2, 2005 at 9:48 am
Windows Auth seems to be a bit slower to me too - no a lot just enough to irritate. And there is an issue that you can't have a linked server from an Win NT box running SQL2000 to a Win 2K+ box running SQL 2000 using Win Auth
James Horsley
Workflow Consulting Limited
August 3, 2005 at 11:27 am
The only thing from a DBA maintenance perspective that I have noticed in the past is that I do not have to create a SQL login and password. You still have to 'grant' the Windows login access to the server, access to the database, membership in server or user database roles, grant/revoke explicit permissions, etc. With the advent of SQL2K5 many of the user/password related management issues that make Windows authetication more appealing from the strength/security perspective seem to diminish to almost nothing.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 17, 2005 at 7:51 am
Windows authentication seems MUCH slower to me. We're a mixed Novell/Microsoft site, primarily using Novell for directory services, printing, etc. and so our AD may not be optimal! Everything I read on securing SQL server suggests using AD. I'd like to follow best practice, but until I can match the authentication performance of a sql login, I can't. Will MS change their best practice recommendations if SQL2K5 makes SQL authentication more secure?
August 17, 2005 at 9:30 am
When you say WA seems slower, are you referring to the intial connection time, or response time during the day while running apps ?
August 17, 2005 at 9:40 am
Just a possible explanation on why WA seems slower ... First it's probably only slower on initial login to the server. I state this because once you are authenticated all of the remaining permissions and such are within the SQL Server. Now for the 'why things may be slower' ... Well you are going out to AD for authentication. This also means that network latency and load between your SQL server and the AD DC's now comes into play in addition to network latency and load between your computer and the SQL Server. I would think that if the additional delay is more than a second or two on your initial login then your site may have network or AD issues to contend with.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 17, 2005 at 9:52 am
I think WA is slower, but we're talking ms and only on connection. The DCs definitely come into play and if you have them separated from the client and SQL server, there could be some latency. I'm not sure that's an issue, however as the time is low.
Unless you are constantly disconnecting and reconnecting.
August 17, 2005 at 9:54 am
I think you're both right. It's not an app login that authenticates once then gets on with it, it's desktop users firing SQL queries from Access or Excel (authentication once per query). Also, DTS users running lots of small packages and development work, also making many separate connections. So the initial login constitutes a large part of the overall session. I'm pretty sure the network is fine, so, probaly the DCs are getting hit and may not be setup to cope with what's thrown at them. It's looks like a case of infrastructure not keeping pace with usage.
Thanks for the comments.
August 17, 2005 at 10:42 am
The delay may be in ms but that is per hop taken and they are cumulative on initiasl login. Dependent on your network topology this can be significantly more than a few ms. This is because the following conversations occur:
Just adding up those numbers by using 'tracert' can be significant. Next, add in the other ms delays involved in processing at each step of the 'WA highway' for the initial 'authentication'. After the initial 'authentication' then all the traffic is between your workstation and the SQL Server. So
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply