Which is the best way to create a connection string for SQL Server?
Should I be using a trusted connection string (windows authentication) or an untrusted connection string (sql server authentication) and why?
My concern is to make my connection string more secure, so it does not get compromised by an attack.
I have read on many forums that Windows Authentication is more secure, and if we use SQL Server Authentication then the user id and password could be sniffed over the network.
Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication. The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method.
When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log into Windows (the OS). This token-based security is more secure than SQL Server's native username/password security.
In other words if you can, choose Windows Authentication. There are times though when you cannot get around SQL Server authentication (legacy application, third-party vendor, etc.).
In my opinion, when it comes how secure the connection is, Windows authentication might works better, however you will be constraint and dependent on that along the way. For example if for some reasons there was a problem in windows users, the issue will flow to SQL accessibility as well. Usually the network and infrastructure person is different from SQL development team, so the DBA would be dependent on them tightly for applying changed on who can login or not.
