Can't access remote database after deployment

Question

I realise there's a lot of 'solutions' out there but none that have resolved my issue.

I have recently created a website and am using iis7.5/sql server 2008 on the production machine.

My database connects on my dev server but does not on the productin machine. My dev machine is sql express 2008.

Here's the connection:

<add name="PingtreeEntities"
  providerName="System.Data.EntityClient"
  connectionString="metadata=
  res://*/;
  provider=System.Data.SqlClient;
  provider connection string=&quot;
  Data Source=localhost;
  Initial Catalog=Pingtree;
  Integrated Security=False;
  User Id=PingtreeUser;
  Password=*********;
  MultipleActiveResultSets=True&quot;" />

This is the message I get:

[SqlException (0x80131904): Cannot open database "Pingtree" requested by the login. The login failed.
Login failed for user 'PingtreeUser'.]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6333056
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +6348014
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6347929
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +352
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +831
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +49
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6349734
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +78
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1938
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +89
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6352606
   System.Data.SqlClient.SqlConnection.Open() +300
   System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +67

[EntityException: The underlying provider failed on Open.]
   System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +11079326
   System.Data.EntityClient.EntityConnection.Open() +142
   System.Data.Objects.ObjectContext.EnsureConnection() +97
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +66
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +47
   System.Linq.Enumerable.SingleOrDefault(IEnumerable`1 source) +271
   System.Linq.Queryable.SingleOrDefault(IQueryable`1 source) +383
   Pingtree.BLL.Data.Applicants.Find(Applicant applicant) in C:\Documents and Settings\John\My Documents\Visual Studio 2010\Projects\Pingtree\BLL\Data\Applicants.cs:32
   Pingtree.BLL.Data.Applications.Test(Applicant applicant, Application application, AcceptedApplication acceptedApplication, ContactPermission contactPermission) in C:\Documents and Settings\John\My Documents\Visual Studio 2010\Projects\Pingtree\BLL\Data\Applications.cs:24
   Pingtree.Public.Test.Add_Click(Object sender, EventArgs e) in C:\Documents and Settings\John\my documents\visual studio 2010\Projects\Pingtree\Pingtree\Public\Test.aspx.cs:107
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3707

I have tried removing EF out of the equation by using a straight connection but still get the same message with a slightly different stack.

Starting to tear my hair out. Help!

Answer 1

Let me insist. Please try to DROP and CREATE both login and db-user in this way:

(note that you must change the password to the one you use)

-- Drop & recreate login
USE [master]
GO
IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'PingtreeUser')
    DROP LOGIN [PingtreeUser];
GO
CREATE LOGIN [PingtreeUser]
    WITH
        PASSWORD=N'password',
        DEFAULT_DATABASE=[Pingtree],
        CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Drop & recreate db-user
USE [Pingtree]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PingtreeUser')
    DROP USER [PingtreeUser];
GO
CREATE USER [PingtreeUser] FOR LOGIN [PingtreeUser]
GO
EXEC sp_addrolemember N'db_owner', N'PingtreeUser'
GO

Answer 2

Make sure that the PingtreeUser has the necessary permissions for the Pingtree database.

1. In MS SQL Server Management Studio, login using sa account or via Windows Authentication
2. Expand .\SQLEXPRESS > Security > Logins folder
3. Double-click PingtreeUser
4. Make sure that Default database is Pingtree
5. Click User Mapping on the left
6. Make sure Pingtree database is checked
7. On the bottom part, check db_owner
8. Click Ok

This should give PingtreeUser the necessary permissions to access the Pingtree database.

Answer 3

The most obvious explanation would be the simplest: that you have the wrong password for that account on that database server. I assume you've verified that

1. The account exists on the SQL 2008 server;
2. You can log in using the password you're using?

Also, why are you referring to "localhost" on the production machine? Is your database on the same machine as your web server? (Not necessarily a good idea.) Either way, you need to use the database server name, not the machine's name on the network.