Trying to figure out correct connectionString in C# CSharp ASP.Net VS2008 with SQL Server 2005 Express

Using the Web Site Administration Tool, I created a couple of users and roles, and a database has been created, but that same tool gives me this error when I click 'Test' under the 'Providers' tab:

Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

I don't see why I need to use that tool, since the Web Site Administration Tool already created the database in the Project's App_Data folder. So I'm assuming it is some kind of connection string issue.

In my machine.config, the connection string is:

<connectionStrings>
            <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

In my web.config, I have tried this:

<connectionStrings>
<add name="LocalSqlServer"
         connectionString=".\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

In my Server Explorer (CTRL+ALT+S), right-click on the db and choosing advanced shows this connection string:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\user1\Documents\Visual Studio 2008\Projects\TryMembership6\TryMembership6\App_Data\ASPNETDB.MDF";Integrated Security=True;User Instance=True

And I have tried modifying my web.config connection string with some of the stuff I saw in the server explorer connection string, but nothing has worked so far. I tried the following:

1) switching the dot (.) with the machine name ROSE-W7\SQLEXPRESS
2) setting integrated security to True from SSPI
3) replacing the DataDirectory with the actual physical path.

Basically, I'm not sure how to get rid of this error. If someone really knows, please help me.
yaronusaAsked:
Who is Participating?
 
AshokCommented:
OR

<connectionStrings>
     <add name="ConnStr"
          connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\aspnetdb.mdf;
          Integrated Security=True;
          User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>
0
 
AshokCommented:
Try

<add key="ConnectionString" value="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName;" />

HTH
Ashok
0
 
AshokCommented:
One of the options from following would definitely work for you.....

Standard Security (SQL Server Authentication)

Option 1 :

Data Source=yourSQLServer; Initial Catalog=yourDB; User Id=yourUserName; Password=yourPwd;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=SQLDevelopment; Initial Catalog=SQL2008; User Id=sql2008; Password=######;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Users]", theSqlServer);

Option 2 :

Server=yourServer;Database=yourDB;User ID=yourUserName;Password=yourPwd;Trusted_Connection=False;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Server=SQLDevelopment; Database=SQL2008; User ID=sql2008; Password=######; Trusted_Connection=False;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Accounts]", theSqlServer);

Trusted Connection (Windows Authentication)

Option 1 :

Data Source=yourSQLServer; Initial Catalog=yourDB; Integrated Security=SSPI;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=SQLDevelopment; Initial Catalog=SQL2008; Integrated Security=SSPI;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Emplpoyees]", theSqlServer);

Option 2 :

Server=yourSQLServer; Database=yourDB; Trusted_Connection=True;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection( "Server=SQLDevelopment; Database=SQL2008; Trusted_Connection=True");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Students]", theSqlServer);

Connecting to the Local SQL Server  Standard Security

Option 1 :

Data Source=(local); Initial Catalog=yourDB; User Id=yourUserName; Password=yourPwd;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=(local); Initial Catalog=SQL2008; User Id=sql2008; Password=######;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Companies]", theSqlServer);

Option 2 :

Server=(local); Database=yourDB; User ID=yourUserName; Password=yourPwd; Trusted_Connection=False;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Server=(local); Database=SQL2008; User ID=sql2008; Password=######;Trusted_Connection=False;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Clients]", theSqlServer);

Option 3 :

Data Source=.; Initial Catalog=yourDB; User Id=yourUserName; Password=yourPwd;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=.; Initial Catalog=SQL2008 User Id=sql2008; Password=######;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Contacts]", theSqlServer);

Option 4 :

Server=.; Database=yourDB; User ID=yourUserName; Password=yourPwd; Trusted_Connection=False;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Server=.; Database=SQL2008; User ID=sql2008; Password=######;Trusted_Connection=False;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Stores]", theSqlServer);

Connecting to the Local SQL Server  Trusted Connection

Option 1 :

Data Source=(local); Initial Catalog=yourDB; Integrated Security=SSPI;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=(local); Initial Catalog=SQL2008; Integrated Security=SSPI;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Teams]", theSqlServer);

Option 2 :

Server=(local); Database=yourDB; Trusted_Connection=True;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection( "Server=(local); Database=SQL2008; Trusted_Connection=True");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Players]", theSqlServer);

Option 3 :

Data Source=.; Initial Catalog=yourDB; Integrated Security=SSPI;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=.; Initial Catalog=SQL2008; Integrated Security=SSPI;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Scores]", theSqlServer);

Option 4 :

Server=. Database=yourDB; Trusted_Connection=True;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection( "Server=.; Database=SQL2008; Trusted_Connection=True");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Grades]", theSqlServer);

Connecting to the Default Database of the User

If the Initial Catalog or Database parameter is not supplied, the database will default to the default database set for the user.

Data Source=yourSQLServer; User Id=yourUserName; Password=yourPwd;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Data Source=SQLDevelopment; User Id=sql2008; Password=######;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Regions]", theSqlServer);

Connecting Via an IP Address

Data Source=123.456.789.012,1433; Network Library=DBMSSOCN; Initial Catalog=yourDB;
User ID=yourUserName; Password=yourPwd;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection("Server=10.0.0.1,1433; Network Library=DBMSSOCN; Initial Catalog=SQL2008; User ID=sql2008; Password=######;");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Sales]", theSqlServer);

Connecting to a SQL Server Instance

Server=yourSQLServer\yourInstanceName; Database=yourDB; Trusted_Connection=True;

SqlConnection Example :

1.SqlConnection theSqlServer = new SqlConnection( "Server=SQLDevelopment\SQL2008Instance; Database=SQL2008; Trusted_Connection=True");
2.SqlCommand theSqlCommand = new SqlCommand("SELECT * FROM [dbo].[Products]", theSqlServer);

Trusted Connection From a CE Device

Data Source=yourSQLServer; Initial Catalog=yourDB; Integrated Security=SSPI;
User ID=yourDomain\yourUserName; Password=yourPwd

Enabling MARS (Multiple Active Result Sets)

Server=yourSQLServer; Database=yourDB; Trusted_Connection=True; MultipleActiveResultSets=true;

Connecting to a Local SQL Server Express Instance

Server=.\SQLExpress; AttachDbFilename=yourMDFFile.mdf; Database=yourDB; Trusted_Connection=Yes;

Connecting to a Local SQL Server Express Instance (Database File in Data Directory)

Server=.\SQLExpress; AttachDbFilename=|DataDirectory|yourMDFFile.mdf; Database=yourDB;
Trusted_Connection=Yes;

HTH
Ashok
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
AshokCommented:
Standard Security
"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

OR

Standard Security
"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

HTH
Ashok
0
 
Kusala WijayasenaSoftware EngineerCommented:
Check following site. It will resolve any connectionstring problem

http://www.connectionstrings.com/

-Kusala
0
 
yaronusaAuthor Commented:
THANK YOU! Please explain to me how simply changing the name of the connection string from LocalSqlServer to anything else like ConnString worked???!!! That's sounds crazy. I did try using LocalSqlServer with the Remove and Clear tag and it didn't work either. So how is it ConnString worked and not LocalSqlServer?
0
 
AshokCommented:
In add Name, you are supposed to use USER-DEFINED name.
I guess "LocalSqlServer" might be confused with Server name or RESERVED WORD.

Another change I see is that you used "Integrated Security=SSPI".

HTH
Ashok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.