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".

Why do you think it is a problem with the connection string? Did you check the sproc? Is it returning data when you call it directly from sql management studio or when stepping through the code? — Raj Kaimal
– Raj Kaimal, Commented Apr 24, 2010 at 17:15
Do you get any error? Instead of "Persist Security Info=true" try this in Connectionstring2 "Integrated Security=True" — Raja
– Raja, Commented Apr 24, 2010 at 17:21
Yes, the sproc returns data correctly in SSMS. However, I set a breakpoint at this part in the C# code behind and am updating this exception above. — salvationishere
– salvationishere, Commented Apr 24, 2010 at 17:22
I had Integrated Security=True before and that worked. However, I do not want to pass passwords over clear text. And I want to enforce security. How safe is IS=true? Can I still use that and encrypt my passwords? — salvationishere
– salvationishere, Commented Apr 24, 2010 at 17:24

Aaronaught · Accepted Answer · 2010-04-24 17:39:42Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related