sql authentication or Windows auth

Question

For my asp.net website with forms authentication, I will use Windows integrated security to access a sql database. I will give DB permissions to the ASPNET or NETWORK SERVICE. Under what circumstances would I use SQL authentication instead?

Answer 1

Use SQL Auth when you need non-Windows machines to make a DB connection.

Keep in mind that it adds another attack vector (another set of credentials to compromise the machine), so make sure you really need it before using it.

Answer 2

SQL authentication is also often required when your site is being hosted by an external ISP. They often do not support windows authentication or do not allow you the ability to grant permissions to windows accounts.

Answer 3

If you want to completely manage user accounts, you should use SQL Authentication. This way, you have complete control over user accounts. You could force them to enter private information for example.

Also, like Corbin mentioned, if clients are not running windows OS, you cannot use Windows Authentication (obviously).

Answer 4

Really you use SQL Authentication when you can't use Windows Authentication. In my opinion that is about the only time. Windows authentication is more secure and can be centrally managed in places which use Active Directory. If you have people who really know how to adminstrate Active Directory and you're in a windows environment, there isn't a good reason to use sql authentication.

With Sql Authentication you have to manage the passwords etc in connection strings and that means that in order to change the account accessing the database, someone has to know how the application functions or at least where the information is stored. With Windows Authentication, all the network admin has to do is enter in the correct username and password into the IIS application settings and you're ready to rock and roll. No developer interaction required.

You have extra steps in securing the connection string information as passwords etc. should be encrypted when stored in the config files. All around there are a lot more steps in invovled in efficiently and securely using Sql Server authentication as opposed to Windows Authentication. This is espcially true if the same sql server credentials are used to access multiple databases across multiple servers.

Answer 5

Please be reminded that Windows Authentication is the recommended mode of authentication for the simple reason that it inherits the OS authentication. There are many factors that you may not use Windows Authentication as explained above.

Answer 6

That depends. If you are developing a in house web app and the network IT people are down the hall then use Windows authentication. If you are deploying your app to customers and you have no control of their network infrastructure then I would use SQL authentication

Answer 7

If you don't have control over the Active Directory (Like in a hosted situation) or there are users with operating systems other than Windows, you don't have a choice.

Is there a need to create user accounts on the fly with some script? It has to be easier to do for a sql user than the Active Directory (Probably not impossible).

Answer 8

Besides all of the above, consider a case like this:

The account you need to use is from Domain A. The database is on Domain B. Domain A and Domain B dont have a trust relationship.

You will need SQL authentication to get past this situation. Hope this helps.

(To add more clarity): The database is NOT registered with the active directory. Then it is not posisble to use windows authentication.

Answer 9

what if i do not have any domain server and all my machines are on workgroup , still i can use windows authentication? or i need to switch to sql authentication ?