Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Login failure to SQL instance in 2012 Expand / Collapse
Author
Message
Posted Sunday, July 7, 2013 4:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1471000
Posted Monday, July 8, 2013 12:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1471046
Posted Monday, July 8, 2013 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1471090
Posted Monday, July 8, 2013 10:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 346, Visits: 310
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.
Post #1471262
Posted Monday, July 8, 2013 7:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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?

Post #1471403
Posted Monday, July 8, 2013 7:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
yes i do have xp_cmdshell enabled. All i see in the job log is Access denied. Its does not say anything else.
Post #1471405
Posted Monday, July 8, 2013 11:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1471443
Posted Tuesday, July 9, 2013 2:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 15, 2014 12:45 AM
Points: 328, Visits: 544
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!

Post #1471478
Posted Tuesday, July 9, 2013 2:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 15, 2014 12:45 AM
Points: 328, Visits: 544
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.....
Post #1471480
Posted Tuesday, July 9, 2013 7:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 346, Visits: 310
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.
Post #1471596
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse