Login failure to SQL instance in 2012

  • Hi All,

    I'm seeing login failures to our end users on our server which has recently migrated to Windows/SQL 2012. Strange thing is if user tries to connect with a service account is works fine. But if he tries to connect by himself it is throwing him login failure error.

    Have anyone seen this issue? I'm also seeing weird things like I'm not able to execute a .cmd file in a SQL job but when i run the same command locally in the server, it works.

    Please help me.

  • Does the account of the user has enough permissions to login the SQL instance and on the database(s)? Take a look in the SQL Logging and determine the "state" and the "severity" of the connection failure. With that you can determine what is the root cause of the login failure.

    About the execution of the CMD file: do you have the setting to allow XP_CMDSHELL enabled?

    Please post error messages and applicable logging where possible. We can give you better help if we have all the information at hands.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Issue 1: Login failure

    Severity: 14, State: 11. is what it shows in the logs.

    In ring buffers below are the error codes.

    [highlight=#ffff11]ErrorCode: 0×534

    CallingAPIName: LookupAccountSidInternal

    APIName: LookupAccountSid[/highlight]

    Which indicates that SQL Cant resolve the name against AD.

    But one of my another SQL instance is also hosted on the same node of the cluster where in the user is having no issues connection.

    I ran the below thing which was intresting to see on 2 different SQL instances that are hosted on the same cluster and same node.

    EXEC xp_logininfo 'Redmond\UserAlias','all'

    On Problematic instance: It did not give me results.

    On other sql instance: It gave me list of all the Security groups the user is part of.

    Oh i should mention this. UAC is enabled on this node.

    can you suggest further?

    Issue 2: CMD execution failure with access denied error.

    we can take this up once Issue 1 is resolved.

  • What happens if you drop and recreate the account?

    When you create an Account the SID gets imported from AD, could be something in AD or the SQL instance changed and now the match is gone. Droping and recreating one of the Windows Login accounts to see if it resolves this will let you know if sp_change_users_login might help resolve your issue.

    Also try creating a new test Database with a test table and test data. Then make this database the windows login default datbaase. Then try to login to the SQL server with this windows login.

    Sometimes (even with AD or Windows Logins) I have seen the SID for the Server Login not match the one for the DB User. If you switch the default database and create a new db user in this new default database and the user can access it, something is wrong the the other databases SID list for the DB users.

    Let us know how this works and what errors you get. Good Luck! 😎

    FYI - Lots of fun things can happen if while trouble shooting someone deleted and/or re-created her AD account before or during you working on this.

  • Thanks a lot the issue is resolved now. Dropping an recreating worked. It looks like its a sure mismatch of SID.

    Now i have another issue.

    SQLCMD is having Access denied error.

    when i execute the SQLCMD using my credential, it works fine. but whjen i put the same command in a SQL job and run it, it fails with Access denied error.

    can you please comment ton this as well?

  • yes i do have xp_cmdshell enabled. All i see in the job log is Access denied. Its does not say anything else.

  • Mac1986 (7/8/2013)


    when i execute the SQLCMD using my credential, it works fine. but whjen i put the same command in a SQL job and run it, it fails with Access denied error.

    Because you run it from a job the permission issue is related to the SQL Agent service account. Does the SQL Agent service account has permissions on the disk to access and execute the SQLCMD.exe file?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • PHYData DBA (7/8/2013)


    What happens if you drop and recreate the account?

    When you create an Account the SID gets imported from AD, could be something in AD or the SQL instance changed and now the match is gone. Droping and recreating one of the Windows Login accounts to see if it resolves this will let you know if sp_change_users_login might help resolve your issue.

    This is the issue I believe. If a database is restored to a new instance, the association between the Instance-level login and the Database-level login is lost because the Security-identifier is different.

    The solution can be one of three options:

    1. Use contained databases in SQL Server 2012 to store the logins for a single database within a single container.

    2. Delete the login in the database and recreate it with in the instance, performing the usual mapping, role assignments etc;

    3. Use the following command:

    USE DB [DB_NAME]

    GO

    EXEC sp_change_users_login 'AUTO_FIX', 'username'

    GO

    Hope something there helps!

  • Mac1986 (7/8/2013)


    yes i do have xp_cmdshell enabled. All i see in the job log is Access denied. Its does not say anything else.

    --Turn on extended configuration options

    exec sp_configure 'show advanced option',1;

    reconfigure;

    --Check to see whether CMDSHELL is turned on (value one is returned)

    exec sp_configure 'xp_cmdshell';

    --If not activated then execute the following

    exec sp_configure 'xp_cmdshell', 1;

    reconfigure;

    If that doesn't work then check the NTFS permissions to the file to ensure that you have access.

    I know you have said it is turned on but it can always be worth checking.....

  • Check the steps in this article on execution with Proxy for just xp_cmdshell:

    http://msdn.microsoft.com/en-us/library/ms175046.aspx/css

    Just turning on xp_cmdshell is not enough on 2012.

    You should look at these articles also:

    http://support.microsoft.com/kb/2160741

    http://msdn.microsoft.com/en-us/library/ms175834.aspx

    Both of these articles give good information on how to work with SQL Server Agent service and create Proxy accounts with proper permissions to execute diferant types of Job steps on SQL 2012.

    The important thing to remember is to give the Proxy account access to ALLthe TEMP directories used by the SQL service and SQL agent accounts.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply