Disadvantages of SQL Server Authentication SQL Server Authentication cannot use Kerberos security protocol. Windows offers additional password policies that aren't available for SQL Server logins. The encrypted SQL Server Authentication login password, must be passed over the network at the time of the connection.
A trusted connection is used if you're connecting to the database without providing a user name and password. For example, if you are connecting via a Data Source Name, and the DSN contains the user ID you're using to connect, then your trusted connection is true.
Mssql Authentication is highly preferable where possible.
SQL Server supports two authentication modes, Windows authentication mode and mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows.
Not 100% sure what you mean:
Trusted_Connection=True;
IS using Windows credentials and is 100% equivalent to:
Integrated Security=SSPI;
or
Integrated Security=true;
If you don't want to use integrated security / trusted connection, you need to specify user id and password explicitly in the connection string (and leave out any reference to Trusted_Connection
or Integrated Security
)
server=yourservername;database=yourdatabase;user id=YourUser;pwd=TopSecret
Only in this case, the SQL Server authentication mode is used.
If any of these two settings is present (Trusted_Connection=true
or Integrated Security=true/SSPI
), then the Windows credentials of the current user are used to authenticate against SQL Server and any user iD=
setting will be ignored and not used.
For reference, see the Connection Strings site for SQL Server 2005 with lots of samples and explanations.
Using Windows Authentication is the preferred and recommended way of doing things, but it might incur a slight delay since SQL Server would have to authenticate your credentials against Active Directory (typically). I have no idea how much that slight delay might be, and I haven't found any references for that.
Summing up:
If you specify either Trusted_Connection=True;
or Integrated Security=SSPI;
or Integrated Security=true;
in your connection string
==> THEN (and only then) you have Windows Authentication happening. Any user id=
setting in the connection string will be ignored.
If you DO NOT specify either of those settings,
==> then you DO NOT have Windows Authentication happening (SQL Authentication mode will be used)
When you use trusted connections, username and password are IGNORED, because SQL Server using windows authentication.
This will probably have some performance costs when creating the connection but as connections are pooled, they are created only once and then reused, so it won't make any difference to your application. But as always: measure it.
UPDATE:
There are two authentication modes:
If your web application is configured to impersonate a client, then using a trusted connection will potentially have a negative performance impact. This is because each client must use a different connection pool (with the client's credentials).
Most web applications don't use impersonation / delegation, and hence don't have this problem.
See this MSDN article for more information.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With