.NET - Best practice for connecting to a data source?

Question

I am building a client side application that will use a SQL Server back end to store persistent data. In the past I have always done this using connection strings, usually which I store in the registry. I then lookup that registry value and use it to initiate my SQLConnection.

However, I am wondering if this method is best practice? The other option that comes to mind would be to create a System ODBC connection on the client machine that could access the data store. This would be a better solution in that the connection string would be less visible, however it also means that the ODBC Connection would need to be created on every machine that has the client installed on it.

What are the best practices for connecting to data stores as stated above?

Answer 1

Your client application is a 2-tier architecture. The best practice would be to use Windows Authentication ("Trusted_Connection=yes" in connection string).

You would manage client access through AD by creating a Group, placing all users in that group, then give the group the required permissions against the SQL Server database.

Choosing an Authentication Mode:

When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

Answer 2

Typically if I'm creating a application that will connect to a central SQL database I use windows / AD authentication to handle the authentication portion of the connection string. The server FQDN or IP is hard coded.

Alternatively if this isn't an option then it would be better to create a service of some kind that the application will interact with instead of connecting directly to the database.

Keeping in mind that these are only general guidelines. If you're talking about a purpose built application for a couple of users within a company then this may all be overkill. Basically it depends on your target audience and requirements.

Answer 3

I use LINQ to SQL, maybe is not the most efficient way for larger databases but if you're familiar with OOP it will be easy to learn and powerful.

Sorry I thought you were talking about database frameworks, in your case use Windows Authentication like Mitch said.