Rules to Better Connection Strings - 5 Rules
Managing connection strings effectively in software development is vital for several key reasons. Firstly, it significantly enhances security; connection strings often contain sensitive data, such as usernames and passwords, which need to be protected from unauthorized access. By properly managing these strings, developers can ensure that this critical information is secured and not inadvertently exposed, especially in source code repositories.
Furthermore, effective management improves maintenance and readability. Storing connection strings in a centralized configuration file simplifies the updating process, as changes can be made in one place rather than scattered across multiple code sections. This centralization also facilitates smoother transitions between different deployment environments, like moving from development to production, where different connection strings might be required. Additionally, avoiding the hardcoding of connection strings in business logic not only averts security risks but also promotes better programming practices. Lastly, a well-managed connection string setup aids in quicker identification and resolution of database connection errors, streamlining troubleshooting processes in database-driven applications. Thus, the importance of managing connection strings effectively lies in bolstering security, simplifying maintenance, increasing deployment flexibility, promoting best programming practices, and facilitating efficient error management.
Different data sources provide different connection strings, for example, the connection string for Oracle is totally different from SQL Server's, even the same product but different versions have special requirement, so how do we make it easy?
The answer is the very useful website called connectionstrings.com, which provides abundant connection strings ranging from different database products to excel files, actually, you can find most of the connection strings of popular products with detail usage.
Both SQL Server authentication (standard security) and Windows NT authentication (integrated security) are SQL Server authentication methods that are used to access a SQL Server database from Active Server Pages (ASP).
We recommend you use the Windows NT authentication by default, because Windows security services operate by default with the Microsoft Active Directory?directory service, it is a derivative best practice to authenticate users against Active Directory. Although you could use other types of identity stores in certain scenarios, for example Active Directory Application Mode (ADAM) or Microsoft SQL Server? these are not recommended in general because they offer less flexibility in how you can perform user authentication.
If not, then add a comment confirming the reason.
<connectionStrings> <add name="ConnectionString" connectionString="Server=(local); Database=NorthWind;uid=sa;pwd=sa;" /> </connectionStrings>Figure: Bad example - Not use Windows Integrated Authentication connection string without comment
<connectionStrings> <add name="ConnectionString" connectionString="Server=(local); Database=NorthWind;Integrated Security=SSPI;" /> </connectionStrings>Figure: Good example - Use Windows Integrated Authentication connection string by default
<connectionStrings> <add name="ConnectionString" connectionString="Server=(local); Database=NorthWind;uid=sa;pwd=sa;" /> <!--It can't use the Windows Integrated because they are using Novell --> </connectionStrings>Figure: Good example - Not use Windows Integrated Authentication connection string with comment
Since we have many ways to use Connection String in .NET 2.0, it is probably that we are using duplicate connection string in web.config.
<connectionStrings> <add name="ConnectionString" connectionString="Server=(local); Database=NorthWind;" /> </connectionStrings> <appSettings> <add key="ConnectionString" value="Server=(local);Database=NorthWind;"/> </appSettings>Bad example - Using duplicate connection string in web.config
We strongly recommend that putting all connection strings together and saving them to one file, it is convenient for management and deployment.
Figure: Bad example - Putting connection strings in Business module You should always add the application name to the connection string so that SQL Server will know which application is connecting, and which database is used by that application. This will also allow SQL Profiler to trace individual applications which helps you monitor performance or resolve conflicts.
<add key="Connection" value="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Biotrack01;Data Source=sheep;"/>Bad example - The connection string without Application Name
<add key="Connection" value="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Biotrack01;Data Source=sheep; Application Name=Biotracker"/> <!-- Good Code - Application Name is added in the connection string. -->Good example - The connection string with Application Name
