SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Login failure to SQL instance in 2012


Login failure to SQL instance in 2012

Author
Message
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 791
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.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 3718
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’! **
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 791
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.
PHYData DBA
PHYData DBA
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 537
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! Cool

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.
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 791
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?
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 791
yes i do have xp_cmdshell enabled. All i see in the job log is Access denied. Its does not say anything else.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 3718
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’! **
kevaburg
kevaburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4447 Visits: 1025
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!
kevaburg
kevaburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4447 Visits: 1025
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.....
PHYData DBA
PHYData DBA
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 537
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search