locked
SQL 2008 - User login issue RRS feed

  • Question

  • We have 2 SQL 2008 servers (sql01 and sql02). This SQL server instance is used to drive our portal as well as other custom applications we’ve built.

    The sql01 is the primary server and always is Principal, and Synchronized. Should this server fail/restart or go offline, the second server sql02 is configured to start up and be the SQL source data driver. When this happens the portal continues to function for the most part. The real issue is (that I can see) is with a service account that we created and use for custom applications.

    The issue is with this service account 'domain\svc_appService'. Anytime SQL02 becomes the primary, anything that uses this service account fails and generates the error shown below.

    The application is giving me this error now….

    Cannot open database "PortalDB" requested by the login. The login failed.
    Login failed for user 'domain\svc_appService'.

    Once we roll back to make SQL01 the primary, this error/issue ‘goes away’.

    Scratching my head here..
    Friday, January 8, 2016 7:26 PM

All replies

  • Did you create a login on the server level for this account? The command looks like this:

    CREATE LOGIN <username> FROM WINDOWS

    You need to do this with every single login you are using.

    Saturday, January 9, 2016 1:45 PM
  • See if this helps you

    http://www.sqlservercentral.com/Forums/Topic1423758-391-1.aspx?Update=1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 10, 2016 6:27 AM
  • Are you saying I should delete the user from the sql02 and create is using the command you provided?

    Could you clarify if this is what you are suggesting I do?

    I don't know how it was originally done, that employee is no longer with us.

    Sunday, January 10, 2016 2:57 PM
  • There are two different things in SQL Server:

    1) The user within the database

    2) The Login on server level

    When you grant permission to a user in an AlwaysOn Scenario you create the login on the primary server + the user in the database. When you subsequently failover the Availability Group the user in the DB is still there on the secondary, but the login is missing on the secondary server. So no, you don't need to delete anything on SQL2, you just need to create the login. And you can do that even while the DB is not running on that node. (The Login does exist on the server level, it has nothing to do with the actual database.)


    [Edit] Sorry... This of course is the same for database mirroring. I am doing too many things at once. ;-)
    • Edited by PrinceLucifer Sunday, January 10, 2016 3:14 PM Important detail...
    • Proposed as answer by Ice Fan Monday, January 11, 2016 12:20 PM
    Sunday, January 10, 2016 3:12 PM
  • I am on the sql02 - currently not the active node, the login already exists on this server...but your telling me I don't need to delete it? You said I just need to create the login, but its already on sql02?
    Monday, January 11, 2016 2:18 PM
  • If it is already on SQL2 then you don't need to do anything. If you failover now the system will work. Just remember to do it with every new login you need.
    Monday, January 11, 2016 4:20 PM
  • No, it doesn't work...that's my original problem issue.

    This user exists on sql02, but I get the error of login failed when this failure over happens....

    So how can I update this user on SQL02 to work?

    Please note this is SQL Server 2008

    Monday, January 11, 2016 4:22 PM
  • What is the error message in the SQL Error Log?
    Monday, January 11, 2016 4:27 PM
  • This is the error the application is giving me...

    Cannot open database "PortalDB" requested by the login. The login failed.
    Login failed for user 'domain\svc_appService'.

    As far as SQL exact error, not sure if its the same as above...is there an easy of checking the sql log, not sure if it would matter since my application is telling me the error that sql is returning?

    Monday, January 11, 2016 4:30 PM
  • Is the error occurring within the OS or SQL Server?

    Re both Servers; are they using this account to start SQL Server Services?

    Does the account have the required permissions to access the Databases; has the account been disabled in SQL Server?


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, January 11, 2016 4:32 PM
  • Reason: Failed to open the explicitly specified database

    please note, not sure if I can really test this now, sql02, is not currently the 'live' database, but the message I am getting now, is still the same message I get when sql02 is the 'live' database.

    Monday, January 11, 2016 4:35 PM
  • Easy way to check is using SQL Management studio.

    Can you please also check the error log on SQL1? And I would appreciate if you could also share the connection string the application uses.

    Monday, January 11, 2016 4:35 PM
  • The error is occurring with a .NET C# web application that I wrote, that uses this service account.

    It all works fine when connecting to sql01, but not sql02.

    As far as permissions, it seems as though they are exactly the same between servers.

    >>has the account been disabled in SQL Server? - why would it be disabled - not sure I understand this.

    Monday, January 11, 2016 4:37 PM
  • Reason: Failed to open the explicitly specified database

    Why would the error show in sql01 if I'm trying to connect to sql02 and getting the error?

    below is the connection string

        <add name="ConnectionString" connectionString="Data Source=dmzsql01.domain.dmz;Integrated Security=SSPI;Initial Catalog=databasename" providerName="System.Data.SqlClient"/>

    below is the user that I am connecting with

        <identity impersonate="true" userName="domain\svc_appService" password="password>

    Monday, January 11, 2016 4:40 PM
  • You are missing the mirroring connection string: There should be an additional "Failover Partner=dmzsql02.domain.dmz;" in there.
    Monday, January 11, 2016 4:42 PM
  • why does that matter?

    even if I change the connection string to point to 02, I still get error

    <add name="ConnectionString" connectionString="Data Source=dmzsql02.domain.dmz;Integrated Security=SSPI;Initial Catalog=databasename" providerName="System.Data.SqlClient"/>

    Monday, January 11, 2016 4:44 PM
  • The additional parameter allows for automatic failover. If your direct point doesn't work we need to look into the SQL Error Log and see the details.
    Monday, January 11, 2016 4:46 PM
  • I think you need to log into SQL Server on SQL2 and have a look at the setup for the account.

    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, January 11, 2016 4:47 PM
  • This is taken out of SQL Log Viewer

    Login failed for user 'domain\svc_appService". Reason: Failed to open the explicitly specified database

    Please note, not sure if I can really test this now, sql02, is not currently the 'live' database, but the message I am getting now, is still the same message I get when sql02 is the 'live' database.

    Monday, January 11, 2016 4:48 PM
  • You can only test it once the DB is live... In SQL 2008 there is no way to simulate this unfortunately. Until the DB is live the error is expected. Once it is live you should get a different error message. (Or it should just work..)
    Monday, January 11, 2016 4:49 PM
  • @Tony C: We are talking DB Mirroring, not failover clustering... The SQL instance is up and running.
    Monday, January 11, 2016 4:50 PM
  • What if I told you the message is exactly the same when the database is live?
    Monday, January 11, 2016 4:52 PM
  • Then I would tell you that you are missing the login on the server as stated above. Or that your DB was actually not live, or that there has to be a second error message in there as well...
    Monday, January 11, 2016 4:54 PM
  • Well the web application provided that error as shown in the initial post. I can't say for certain what the sql log would say for 'reason'.

    Are you thinking it may be different?

    Monday, January 11, 2016 4:56 PM
  • The error message only ever happens for two reasons:

    1) The user cannot login on the server (and therefore subsequently not authorize against the DB)

    2) The database is not in an ONLINE state.

    So whatever of those problems you are facing, that's what you need to fix. And the SQL Errorlog is the point to look. (And you should still have that error message in SQL02 error log from your last failover attempt...)

    Monday, January 11, 2016 4:58 PM
  • Hi,

    Also Just check @Login Properties, Login has the default database set to the mirrored DB. Some times this cloud be the issue having login defaulting to a database that it doesn't have access to.

    Hope this helps

    Thanks

    Bhanu   

    Monday, January 11, 2016 7:15 PM