I have a development website that is written in ASP.NET 2.0 with a SQL 2005 backend. In my unpublished version I was using ASPNETDB.mdf which is the default security setup for .NET. When I found out that HostMySite does not support this, I ran Aspnet_regsql.exe on the live database I have with HostMySite. When I try to use the .NET membership API to create a user, I get the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I tried copying all of my admin section pages to a non-protected folder and they work just fine (inserts, updates, deletes). I have put in a ticket with HostMySite but they are taking too long and I need an answer. Code samples are below:

Connection String:

<remove name="CMI"/>
<add name="CMI" connectionString="Server=geyser.safesecureweb.com;integrated security=sspi;Uid=user;Pwd=password;Database=db;Trusted_Connection=no;"/>

Membership:

<remove name="AspNetSqlMembershipProvider"/>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="CMI" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>

VB Code to add Admin role + 1 user:

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim Username As String = Me.rtxtUsername.Text
Dim Password As String = Me.rtxtPassword.Text
Dim Email As String = Me.rtxtEmail.Text

Dim sConnString As String = ConfigurationManager.ConnectionStrings("CMI").ConnectionString
Dim objConnection As New SqlClient.SqlConnection(sConnString)


Try
Dim createStatus As MembershipCreateStatus
Roles.CreateRole("Admin")
Membership.CreateUser(Username, Password, Email, "What is your email address?", Email, True, createStatus)
If createStatus = MembershipCreateStatus.Success Then
Roles.AddUserToRole(Username, "Admin")
lblCreateUserStatus.Text = "The user account was successfully created!"
lblCreateUserStatus.ForeColor = Drawing.Color.Green
Else
Select Case createStatus
Case MembershipCreateStatus.DuplicateUserName
lblCreateUserStatus.Text = "There already exists a user with this username."
Exit Sub
Case MembershipCreateStatus.DuplicateEmail
lblCreateUserStatus.Text = "There already exists a user with this email address."
Exit Sub
Case MembershipCreateStatus.InvalidEmail
lblCreateUserStatus.Text = "There email address you provided in invalid."
Exit Sub
Case MembershipCreateStatus.InvalidAnswer
lblCreateUserStatus.Text = "There security answer was invalid."
Exit Sub
Case MembershipCreateStatus.InvalidPassword
lblCreateUserStatus.Text = "The password you provided is invalid. It must be seven characters long and have at least one non-alphanumeric character."
Exit Sub
Case Else
lblCreateUserStatus.Text = "There was an unknown error; the user account was NOT created."
Exit Sub
End Select
End If

Catch ex As Exception
Response.Write(ex.Message)
Response.End()
Finally
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try
End Sub

I use this code in all of my websites and I have never had a problem. Could it possibly be a permissions issue? It seems that I am able to insert, update, and delete but I can't run stored procedures?

This has been fixed. I had to create a second connection string for the membership api to work:

<remove name="LocalSqlServer" />
<add name="LocalSqlServer"
connectionString= "Server=geyser.safesecureweb.com,1433;Database=cmibuilds;User=cmibuilds;Password=C0nstructi0n;"
providerName="System.Data.SqlClient" />