SQL Server vs Windows Authentication
-
Hi,
At the latest Microsoft UK TechRoadShow one of the seminars on DB security preached as an absolute need the decision to use ONLY Windows Authentication for the highest level of security. I see from the latest messages on this thread, and others before, that a lot of people are using SQL Server Authentication. So I would like to understand: is there a real security risk?
For us using Windows Authentication would mean having to manage users through Windows, and as a result putting the user management back into our court. Using the method described in the "Extranet SQL Server ASP" thread (http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=119906) it can all be managed by the clients via the database. We've even added other user attributes such as account duration, password expiry periods, etc.
In our case we are quite carefull about the priviliges we give to the single SQL Server login accounts so we think that the "SQL Authentication" is quite secure. Of course I have to trust with all my soul
what our network guy has done in terms of firewalls, network security etc.So fire away, I'm all ears.
Thanks
-
The main thing is that SQL Auth uses sends the password in plain text accross the network. Windows Auth doesn't.
-
Well if that is the main difference, I would conclude that in our case SQL Auth is perfectly adequate PROVIDED our network security holds up. The trade-off in having us manage users is not feasible and the sites are not handling critical data anyway. But I will point out the issue to our security guy.
-
Also if you are managing a lot of users it's far easier to manage their access through Windows groups as opposed to individual logins.
-
Very good point. We are a small SW house, and as I pointed out above using SQL Auth and putting the user mgmt. in the database allows us to pass all that onto the web application Admin users. They can create, delete users, modify passwords, account durations etc without putting a strain on our limited resources AND - vital from a security point of view - having no knowledge of SQL accounts. OK we've had to be carefull to guard against SQL injection, but apart from that we've had no problems (up until now :whistling
-
This is the connection string from my ASP code:
PROVIDER=SQLOLEDB.1;DATA SOURCE=MachineName;INITIAL CATALOG=DatabaseName;USER ID=userid;PASSWORD=password;
In case I want to swicth to Windows Authentication Mode
how would it change?
If I change to Windows Authentication mode
I guess IUSR_MachineName comes into place (am I right?).
but how SQL Server knows what access IUSR_MachineName account was granted?
-
You would change USER ID = and PASSWORD = to Intergrated Security = SSPI
-
Integrated Security. OK.
I'm using SQL Server
for web application running at WebHosting company.
Internet users are being authenticated as IUSR_MachineName users at the Web Server. Web server communicates with
SQL Server through that connection string I posted.
So how SQL Server knows
what privileges IUSR_MachineName account
was granted?
I mean with SQL Server Authentication
I can control for example what db a user can access.
How do I do it using Windows Authentication?
-
You could change the IUSR account to a normal account.
-
I have never heard
that somebody modified IUSR_ account.
It's a built-in IIS account.
If you touch it it might affect
other things on your system.
But still.
Even if I have full control over IUSR account
how SQL Server knows what rights it has???
-
Hi Riga,
Following up on the previous reply what you need to do is the following:
a) Create a new login in SQL
b) Instead of entering a login name, press the browse button "..." and select which Domain/Windows account you want to add
c) Grant all the necessary privileges to that account, as you would with a SQL Account
In practice you would create a new Windows account for each application/database, and grant that accoutn the minimum priviliges required.
Hope that helps
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply