Vba Asking Sql Server Login Although Uid Pwd Is in Connection String

The ultimate guide to connection strings in web.config

TOC

Connectedness strings in web.config tin can exist both powerful and challenging. The times were y'all would just remote desktop to production and alter settings inside your ASP.NET web.config file is long gone. And so, should you go along specifying connection strings using the connectionStrings element in web.config? Stay tuned to learn everything in that location is to learn about the subject.

The ultimate guide to connection strings in web.config

Let me commencement this mail service by providing an example to make sure that everyone follows. Inside the web.config file (and app.config file too), there'due south a root chemical element named connectionStrings. The chemical element works a flake like appSettings where you lot can add one or more connectedness strings for your application to employ on runtime. The configuration looks similar to this:

              <?xml version='1.0' encoding='utf-8'?>   <configuration>     <connectionStrings>       <add proper noun="myConnection" connectionString="server=localhost;database=mydatabase;" />   </connectionStrings> </configuration>

Fetching the connectedness cord is washed using the ConfigurationManager class:

              string conn = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;

At starting time glance, this looks like a more complicated version of ConfigurationManager.AppSettings and when used this fashion, it actually is. Clever things happen when database vendors or ORMs built-in back up for the connectionStrings element. Like Entity Framework where connexion string names can be referenced when creating the context:

              public class DatabaseContext : DbContext {     public DatabaseContext() : base("myConnection")     {     } }

Connection strings for pop databases

SQL Server

Since about questions I get about connection strings are related to SQL Server, allow's start by looking at the possibilities there. The connectedness cord will be identical no thing if you are connecting through Entity Framework, NHibernate, or using the raw SQL connection available in .NET.

The simplest SQL Server connection string looks like this:

              <connectionStrings>     <add     name="sqlServer"     providerName="System.Information.SqlClient"     connectionString="Information Source=localhost;Initial Catalog=MyDatabase;Integrated Security=Truthful;" /> </connectionStrings>

There are a couple of new things to notice. The providerName attribute tells users of the connectedness cord which .NET Framework Data Provider to use when communicating with the database. The content of the connectionString aspect tells them which server to communicate with and the name of the database. The Integrated Security=True part will employ the Windows user executing the program to log onto SQL Server (Windows Authentication using Integrated Security). To brand sure the current user is authenticated, open Management Studio, aggrandize the Security node, and verify that the current user is added:

Before we move on, I desire to mention a couple of commonly used settings.

Connexion Timeout

Being able to change the connexion timeout through web.config tin be very useful when deploying the same application on dissimilar environments. Luckily, this is achievable through a unproblematic modification:

              <connectionStrings>     <add     name="sqlServer"     providerName="Arrangement.Data.SqlClient"     connectionString="Information Source=localhost;Initial Itemize=MyDatabase;Integrated Security=True;Connection Timeout=60" /> </connectionStrings>

In the case, I've added Connection Timeout=lx to allow the SQL connection lx seconds to connect to SQL Server (the default value is 15 seconds). Notice that this setting doesn't control command timeout, which is the time allowed for querying one or more tables. Command timeout is non accessible through spider web.config.

Username/countersign

              <connectionStrings>     <add     name="sqlServer"     providerName="System.Information.SqlClient"     connectionString="Data Source=localhost;Initial Itemize=MyDatabase;User Id=user;Password=pwd;" /> </connectionStrings>

MySQL

Connecting to MySQL is as easy as SQL Server. Afterwards installing either the MySQL .Net Connector or the MySQL NuGet packages, there's a new provider available through config:

              <connectionStrings>     <add     name="mySql"     providerName="MySql.Data.MySqlClient"     connectionString="Server=localhost;Database=MyDatabase;Uid=user;Pwd=pwd;" /> </connectionStrings>

There are a lot of options bachelor through the connection string to MySQL. Hither are a few of the ones I have used in the past.

Integrated security

              <connectionStrings>     <add     proper noun="mySql"     providerName="MySql.Data.MySqlClient"     connectionString="Server=localhost;Database=MyDatabase;IntegratedSecurity=yes;Uid=auth_windows;" /> </connectionStrings>

Connection pool size

              <connectionStrings>     <add together     name="mySql"     providerName="MySql.Data.MySqlClient"     connectionString="...;MinimumPoolSize=10;maximumpoolsize=50;" /> </connectionStrings>

Encryption

              <connectionStrings>     <add     name="mySql"     providerName="MySql.Data.MySqlClient"     connectionString="...;SslMode=Required;" /> </connectionStrings>

Connexion string encryption

In case you cannot use Integrated Security/Windows Authentication, you lot may have a username and password in clear text inside the spider web.config file. For obvious reasons, you lot really don't want that. If a hacker gets access to your spider web server, he/she now has access to your database besides. Encryption to the rescue.

Encrypting connection strings is pretty straightforward, using the aspnet_regiis tool part of the .NET framework. In most cases, you want encrypted settings on your staging and production environments only, why this footstep could be part of your deployment pipeline.

To encrypt the entire connectionStrings element, run the post-obit control:

              aspnet_regiis -pef "connectionStrings" "c:\path\to\the\binder\containing\webconfig"

The pef parameter tells aspnet_regiis to encrypt the content of the connectionStrings chemical element but and the last parameter points out the folder containing the web.config file. Detect that the path should be to a folder and not the total path of the web.config file. Also, make sure to run cmd equally Administrator.

If you have already deployed your application to IIS, you lot can encrypt it with a similar control:

              aspnet_regiis -pe "connectionStrings" -app "/"

Both commands modify the web.config file to at present contain encrypted values:

              <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">   <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"     xmlns="http://www.w3.org/2001/04/xmlenc#">     <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />     <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">       <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">         <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />         <KeyInfo xmlns="http://world wide web.w3.org/2000/09/xmldsig#">           <KeyName>Rsa Central</KeyName>         </KeyInfo>         <CipherData>           <CipherValue>A long nil value</CipherValue>         </CipherData>       </EncryptedKey>     </KeyInfo>     <CipherData>       <CipherValue>Another cipher value</CipherValue>     </CipherData>   </EncryptedData> </connectionStrings>

At that place's no need to change whatever C# lawmaking to make encrypted connection strings work. ConfigurationManager automatically decrypts connection strings from web.config.

Encrypted data tin can fifty-fifty be converted dorsum to their original values by using the decryption command:

              aspnet_regiis -pdf "connectionStrings" "c:\path\to\the\folder\containing\webconfig"

I know, pdf looks totally weird in this context, but it is short for provider decrypt file or something like that :)

Replace config with Web.config transformations

You probably don't use the same database for local development and running your production environment. Being able to alter a connexion string depending on which surround your code is currently running, can be washed in a number of means. Cloud providers like Azure already have born back up for overriding a connexion string through app service configuration. But for someone not running on Azure, replacing connection strings can be washed using Web.config Transformations.

To use another connection string when your lawmaking is running in release configuration, create or open the file named Web.Release.Config and paste the following configuration:

              <?xml version="ane.0"?> <configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">   <connectionStrings>     <add together        name="myConnection"        connectionString="server=myprodserver;database=mydatabase;"        xdt:Transform="SetAttributes"        xdt:Locator="Match(name)"/>   </connectionStrings> </configuration>

When yous deploy your website, the myConnection connection string will override the one already specified in the Web.config file. This is automatically done by Web.config Transformations and the xdt:Transform and xdt:Locator attributes. For more than information and examples of Spider web.config Transformations, check out the following post: Web.config transformations - The definitive syntax guide. Besides, make certain to validate your Web.Release.Config file with the Web.config Transformation Tester tool.

Validating and fault monitoring

Writing and testing connection strings mostly require you to launch your project and examination the connection through a working site in the browser. Visual Studio has IntelliSense for the overall XML structure but not the content within the attributes. I always recommend people to use the Web.config Validator to validate that the XML function is correct. If y'all are replacing production-specific connection strings with Web.config transformations, make certain to validate your transformation with the Web.config Transformation Tester tool.

Every bit for monitoring for errors, I recommend you implement a good mistake monitoring solution on your website running in production. Every bit the founder of elmah.io, I want to recommend you to use that. But there are like solutions out there that will become y'all almost every bit far. Just promise me not to log errors in log files that no i will ever wait through. Getting mistake notifications through emails, Slack, Teams, or similar should be essential for all .Internet web developers.

elmah.io: Error logging and Uptime Monitoring for your web apps

This blog mail is brought to you past elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify y'all when something is wrong or dig through hundreds of megabytes of log files spread beyond servers. With elmah.io, we store all of your log letters, notify you lot through popular channels similar email, Slack, and Microsoft Teams, and help y'all fix errors fast.