How to access web.config connection string in C#?

Question

I have a 32-bit XP running VS 2008 and I am trying to decrypt my connection string from my web.config file in my C# ASPX file.

Even though there are no errors returned, my current connection string doesn't display contents of my selected AdventureWorks stored procedure.

I entered it:

C:\Program Files\Microsoft Visual Studio 9.0\VC>Aspnet_regiis.exe -pe "connectionStrings" -app "/AddFileToSQL2"

Then it said "Succeeded".

And my web.config section looks like:

  <connectionStrings>
    <add name="Master" connectionString="server=MSSQLSERVER;database=Master; Integrated Security=SSPI"
      providerName="System.Data.SqlClient" />
    <add name="AdventureWorksConnectionString" connectionString="Data Source=SIDEKICK;Initial Catalog=AdventureWorks;Integrated Security=True"
      providerName="System.Data.SqlClient" />
      <add name="AdventureWorksConnectionString2" connectionString="Data Source=SIDEKICK;Initial Catalog=AdventureWorks;Persist Security Info=true; "
  providerName="System.Data.SqlClient" />
  </connectionStrings>

And my C# code behind looks like:

    string connString = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString2"].ConnectionString;

Is there something wrong with the connection string in the web.config or C# code behind file?

I set a breakpoint in the C# code behind and now I get the exception below:

System.Data.SqlClient.SqlException was caught
  Message="Login failed for user ''."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=14
  LineNumber=65536
  Number=18456
  Procedure=""
  Server="SIDEKICK"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at ADONET_namespace.ADONET_methods.DisplaySchemaTables() in C:\Documents and Settings\Admin\My Documents\Visual Studio 2008\Projects\AddFileToSQL2\AddFileToSQL\Admins\ADONET methods.cs:line 65
  InnerException: 

Also, I added a LoginView web control to secure my website. The login name is "tester".

Answer 1

The connection string you're using is this:

Data Source=SIDEKICK;Initial Catalog=AdventureWorks;Persist Security Info=true;

That's wrong. You don't have Integrated Security=True, which means it won't use Windows authentication. And you don't have a User Name/Password defined either, so it won't use any SQL Server login.

So your connection string is trying to log in without any credentials, which is why you get that error message.

To fix it, you need to put Integrated Security=True back (to use the current Windows user identity), or you need to put in a specific user name and password.

---

Also, reading your comments, please note the difference between an un-enecrypted connection string and sending a password over clear text:

• An encrypted connection string is useful when you are storing credential information (such as a password) in your web.config file. If somebody manages to get their hands on the web.config, they can't see the password.

• However, even if you encrypt the connection string, if the connection string has a user name and password then that information is being sent in clear text between the web server and SQL Server. Using Integrated Security, however, does not send any credentials over clear text, regardless of whether or not you encrypt the connection string or web.config. That is the reason to use it; Integrated Security means that whichever Windows account is already logged in will be used to authenticate with SQL Server.