Integrated or SQL logins?

Hi,

I'm relatively new to the admin side of websites and DBs. In the past, with hosted environments I've been used to just using SQL logins to connect to the DB from a web app, but for something I'm delevoping at the moment I have control over the DB and web server and was wondering if using a trusted connection would be better.

After reading around it seems that Microsoft warns against using mixed mode authentication because of clear text passwords, the DB I'm working again currently isn't in mixed mode.

So far I have set up the application to run in its own application pool under a limited user account that also has limited access to the DB. Though like this every new application that needs access to a different DB would require a different pool.

What I'm asking essentially is what's the best way of going about things; set up a new pool and user for each app that requires it, using a trusted connection or turn on mixed mode authentication on on the DB. Or a mixture of the two?

magicmonkey:

Hi,

After reading around it seems that Microsoft warns against using mixed mode authentication because of clear text passwords, the DB I'm working again currently isn't in mixed mode.

Those warnings are from Windows engineers who do not know DCL(data control language) of the relational model, the only thing a trusted connection do for you is to use an existing Windows Domain account to connect to SQL Server but you still need a SQL Server account and the relevant database permissions. The only difference with Mixed is that account may not be a Windows account. If you are interested in security then look up object permissions, access to the server is not access to database, access to database is not access to tables and other objects and all of the above is in DCL. Run a search for object permissions in SQL Server BOL(books online). Hope this helps.

|||

Those warnings are from Windows engineers that have a much better grasp of security than Caddre. A trusted connection does a lot more for you than Caddre would lead you to believe:

A) Trusted Connections doesn't transmit user ids and passwords in clear text.

B) Trusted Connections can encrypt the data across a network (Mixed mode can not).

C) Trusted Connections use the same security lockout rules as your domain (Mixed mode does not have *ANY* lockout rules opening your database to the possibility of a brute force attack).

D) Because mixed mode connections don't have encryption, it's fairly trivial to set up a man-in-the-middle attack.

E) In a corporate environment, password changes in the domain (or account being disabled) is carried over to the database security. Database accounts can be "forgotten" about when a user leaves and/or is terminated. With a trusted connection, either the password was random and noone knew what it was in the first place (Like LocalSystem), or it can be easily changed without affecting any application code.

I'm sure there are other reasons as well, and I'm not proclaiming to be a SQL Server security expert or cryptographer myself. But any one of these would lead me to the following answers:

If your network is not 100% secure (transmits over the internet, you don't have physical control of every place the network cables are, or you are using a wireless network anywhere), then don't used mixed mode if security is important.

If the data in your database needs to be highly secure, then don't use mixed mode.

If your security needs go beyond keeping the secretary and Jr programmers from accessing parts of the database then don't use mixed mode.

|||I don't agree because a trusted connection is still open to any attack if you don't use correct object permission, to be really secure you need SQL context permission where all systems use the same account.|||

No, a trusted connection is not susceptable to man in the middle, or brute force attacks as I mentioned. The actual data isn't privy to being seen by unauthorized people as it's being transmitted over the network.

You are talking about permissions. Permissions is what a particular user is allowed/not allowed to do or see. Which is great if you are sure the person you are talking to is who you think it is.

The problem is in the authentication. The mixed-mode authentication security is weak.

|||That is what the context account was created for in SQL Server where everything uses two or three accounts to connect to SQL Server.