SQL Connection String Assistance

I'm trying to upload the personal website starter kit to a web hosting site, and cannot get the connection string correct.

I want to use the personal.mdb in the App_Data folder within my site, which works fine locally, but once uploaded, I get all kinds of errors - depending on how I mess with the string. In no interation can I get the string to work when trying to use the <AttachDBFilename> entry.

When just adding the connection string provided by the hosting service to connect to their server, it opens, but obviously none of the website database functionality works.

I am assuming that I will be using the SQL engine on the server, and pointing it to my database file.

Here is the complete database connection string from the personal website starter kit:

Here is the connection string as provided by my hosting service:

Data Source=whsql-xxxxxx.xxxx.mesa1.secureserver.net;Initial Catalog=DB_xxxxx;User ID=mxxxx;Password=abc123xxx;

Here is one of the errors I've gotten:

Invalid value for key 'attachdbfilename'.

Line 5: 	Sub Application_Start(ByVal sender As [Object], ByVal e As EventArgs)
Line 6: 		AddHandler SiteMap.SiteMapResolve, AddressOf Me.AppendQueryString
Line 7: 		If (Roles.RoleExists("Administrators") = False) Then
Line 8: 			Roles.CreateRole("Administrators")
Line 9: 		End If

Thanks for the help!

Matt

Answer this question

SQL Connection String Assistance

Sree Prakash

I have a like problem. Can you help me

An attempt to attach an auto-named database for file e:\wwwdata\LocalUser\lubos.9d5aae\www\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file e:\wwwdata\LocalUser\lubos.9d5aae\www\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 5: 	Sub Application_Start(ByVal sender As [Object], ByVal e As EventArgs)
Line 6: 		AddHandler SiteMap.SiteMapResolve, AddressOf Me.AppendQueryString
Line 7: 		If (Roles.RoleExists("Administrators") = False) Then
Line 8: 			Roles.CreateRole("Administrators")
Line 9: 		End If

Source File: e:\wwwdata\LocalUser\lubos.9d5aae\www\global.asax Line: 7

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file e:\wwwdata\LocalUser\lubos.9d5aae\www\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
  System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
  System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
  System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
  System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
  System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
  System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
  System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
  System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
  System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
  System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
  System.Data.SqlClient.SqlConnection.Open() +111
  System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
  System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
  System.Web.Security.SqlRoleProvider.RoleExists(String roleName) +482
  System.Web.Security.Roles.RoleExists(String roleName) +242
  ASP.global_asax.Application_Start(Object sender, EventArgs e) in e:\wwwdata\LocalUser\lubos.9d5aae\www\global.asax:7

</connectionStrings>

Where I wrong. Thank you!

neostylo

that is correct, both .mdf's are in the correct location on the hosting site.

Thanks,

Matt
best one
Matt,

This was a bit surprising at first but I finally understand what's happening. When you use AttachDbFileName in conjunction with |DataDirectory| and the Data Source keyword, we enforce that the client machine name is the same as the Data Source machine name. What I'm assuming is happening on your hosters machine is that the Data Source name, whsql-XXXXXXX.mesa1.secureserver.net, is in fact a different name than the ASP.NET Web Server that your pages are being hosted on. We require that the SQL Server is installed on the local box in order to take advantage of |DataDirectory| in your connection string. So in order to use it you'll need to have your hoster provide you access to a Sql Server install (SqlExpress would work) on the same machine. The change Data Source to be equal to the local machine and it will start working.

Does this make sense Can you verify this works for you

Thanks

Carl Perry

ADO.NET Program Manager
Holywhippet
Hello,

I have the same problem. What is the way around if the hosting provider doesn't allow SQLExpress or SQL Server Install

Thanks
Nick Coppola

You may be missing the backslash as a directory separator - did you try

|DataDirectory|\Personal.mdf

as suggested by http://msdn2.microsoft.com/en-us/library/ms254499.aspx
citifortune concepts
Hi Carl

Thanks for this post - its the first sensible one I've seen on this topic (which seems to be an incredibly common problem).

I have the same problem in that my web server & SQL server (at a hosting company) are different. My workaround has been to create the db/tables in SQL 2005 using the SQL script for this db that is supplied with the project and then change my web.config connection to reflect this:

<connectionStrings>
<clear/>
<add name="Personal" connectionString="Server=SQLB12.webcontrolcenter.com;Database=markgalliers;User ID=markgalliers;Password=kat5112; " providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=.\personal\App_Data\aspnetdb.mdf" />
</connectionStrings>

However, my problem now is that I don't have the aspnetdb.mdf db or SQL script for it, besides which I'm not sure when this can also be transferred to SQL 2005

Do you have any idea whether this is possible, or even better whether you have a SQL script for this db

Thanks in advance
Mark
Yuvaraj_Amar

Thanks for that reply. I understand now the limitations of my hosting service and what I need to do to use SQL and the personal web site.

Thanks for the assistance, I do appreciate it!!!

Matt
known2none

Is the aspnetdb.mdf file in the Data directory on the target machine

I am moving this to the ".NET Framework Data Access and Storage", which has a better chance answering the question.
lookat

Here is my current connection string, with theedit from the last post:

<connectionStrings>

<add name="Personal" connectionString="Data Source=whsql-xxxxxxxx.mesa1.secureserver.net;Initial Catalog=DB_xxxxx;User ID=mattk;Password=xxxxxx;AttachDBFilename=|DataDirectory|\personal.mdf" providerName="System.Data.SqlClient"/>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=whsql-xxxxxxx.mesa1.secureserver.net;Initial Catalog=DB_xxxxx;User ID=mattk;Password=xxxxxx;AttachDBFilename=|DataDirectory|\aspnetdb.mdf" providerName="System.Data.SqlClient"/>

</connectionStrings>

Unfortunately it did not have any effect on the problem.

Thanks

Matt
SQL Connection String Assistance

Answer this question