October 9, 2017 at 1:14 pm
Hey folks- thanks in advance if you can help with this.
I run several SQL Server 2014 SP1 clusters. When we deploy a new one, we copy the logins using sp_help_revlogin. Windows accounts get created with a statement exactly like this:
CREATE LOGIN [OurDomain\OurLogin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
No error is generated at login creation time, but attempts to log in with the windows login result in the error:
Error: 18456, Severity: 14, State: 11
Login failed for user 'OurDomain\OurLogin'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
When I check sys.syslogins, I can see that hasaccess=0... The kicker, I drop and recreate the login using the exact same syntax a second time and it is created correctly. Anyone seen this before?
October 9, 2017 at 6:35 pm
NJ-DBA - Monday, October 9, 2017 1:14 PMHey folks- thanks in advance if you can help with this.I run several SQL Server 2014 SP1 clusters. When we deploy a new one, we copy the logins using sp_help_revlogin. Windows accounts get created with a statement exactly like this:
CREATE LOGIN [OurDomain\OurLogin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
No error is generated at login creation time, but attempts to log in with the windows login result in the error:
Error: 18456, Severity: 14, State: 11
Login failed for user 'OurDomain\OurLogin'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.When I check sys.syslogins, I can see that hasaccess=0... The kicker, I drop and recreate the login using the exact same syntax a second time and it is created correctly. Anyone seen this before?
Seems to be exactly what is reported in this blog:
Solution - Login failed for user x. Reason Token based server access validation failed and error - 18456
Sue
October 9, 2017 at 8:42 pm
Sue_H - Monday, October 9, 2017 6:35 PMNJ-DBA - Monday, October 9, 2017 1:14 PMHey folks- thanks in advance if you can help with this.I run several SQL Server 2014 SP1 clusters. When we deploy a new one, we copy the logins using sp_help_revlogin. Windows accounts get created with a statement exactly like this:
CREATE LOGIN [OurDomain\OurLogin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
No error is generated at login creation time, but attempts to log in with the windows login result in the error:
Error: 18456, Severity: 14, State: 11
Login failed for user 'OurDomain\OurLogin'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.When I check sys.syslogins, I can see that hasaccess=0... The kicker, I drop and recreate the login using the exact same syntax a second time and it is created correctly. Anyone seen this before?
Seems to be exactly what is reported in this blog:
Solution - Login failed for user x. Reason Token based server access validation failed and error - 18456Sue
It does look very similar. In our case the Sids are correct though
October 10, 2017 at 9:16 am
I figured this out today. The problem occurs when a linked server is created that specifies a local login before that local login is created. For example, if I create a linked server like this:
EXEC master.dbo.sp_addlinkedserver @server = N'myservername', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myservername',@useself=N'False',@locallogin=N'mydomain\mylogin',@rmtuser=N'asqloging',@rmtpassword='asqlpassword'
On a server where the login mydomain\mylogin does not exists, SQL Server will create the login, but it will create it with hasaccess=0 (status=10). When I later try to create the login using normal syntax, it statement succeeds, but it never gives it local access.
October 10, 2017 at 9:42 am
You're just using the CREATE LOGIN statement? I get as far as verifying that the user got created with hasaccess=0, but when I run CREATE LOGIN it always kicks back "The server principal 'mydomain\mylogin' already exists".
October 10, 2017 at 10:04 am
SQLPirate - Tuesday, October 10, 2017 9:42 AMYou're just using the CREATE LOGIN statement? I get as far as verifying that the user got created with hasaccess=0, but when I run CREATE LOGIN it always kicks back "The server principal 'mydomain\mylogin' already exists".
That's a good point. We do get the "server principal already exists" error, but we had been ignoring it. What happens is that we have a number of logins which are created at install time (##MS_PolicyTsqlExecutionLogin##, sa , and a few others). Then, when the installation is complete, we create other logins, linked servers, SQL Agent jobs and Alters by scripting them out from another server. Those logins which were created during the install will already exist, so we expect this error and maybe don't notice that the list of logins that existed previously is larger than usual if someone goofs and creates the linked servers first.
October 10, 2017 at 10:29 am
NJ-DBA - Tuesday, October 10, 2017 10:04 AMThat's a good point. We do get the "server principal already exists" error, but we had been ignoring it. What happens is that we have a number of logins which are created at install time (##MS_PolicyTsqlExecutionLogin##, sa , and a few others). Then, when the installation is complete, we create other logins, linked servers, SQL Agent jobs and Alters by scripting them out from another server. Those logins which were created during the install will already exist, so we expect this error and maybe don't notice that the list of logins that existed previously is larger than usual if someone goofs and creates the linked servers first.
Kind of demonstrates why ignoring errors or assuming it's okay to bypass errors can become a problem.
Logins enclosed in ## are certificate based logins for internal use only. They should really be left alone.
I'd suggest a script for logins that doesn't have errors to ignore so when you get errors, you know you have something you need to look into.
Sue
October 10, 2017 at 10:36 am
Sue_H - Tuesday, October 10, 2017 10:29 AMNJ-DBA - Tuesday, October 10, 2017 10:04 AMThat's a good point. We do get the "server principal already exists" error, but we had been ignoring it. What happens is that we have a number of logins which are created at install time (##MS_PolicyTsqlExecutionLogin##, sa , and a few others). Then, when the installation is complete, we create other logins, linked servers, SQL Agent jobs and Alters by scripting them out from another server. Those logins which were created during the install will already exist, so we expect this error and maybe don't notice that the list of logins that existed previously is larger than usual if someone goofs and creates the linked servers first.Kind of demonstrates why ignoring errors or assuming it's okay to bypass errors can become a problem.
Logins enclosed in ## are certificate based logins for internal use only. They should really be left alone.
I'd suggest a script for logins that doesn't have errors to ignore so when you get errors, you know you have something you need to look into.Sue
Yeah, kind of obvious at this point
October 10, 2017 at 10:51 am
I know as the DBA where I work, if a script has errors when run, I run the rollback script and then contact the developer. We have a "No script should have errors when deployed on live" policy at work and it has saved us a few times. If the script says something like "--this will generate an error at times", then we require the developer to adjust the script to throw no errors before it gets onto the live system.
I agree with Sue_H in that I'd recommend fixing the script. I'd also recommend having it sitting on a network share ready for re-run at instance creation. We tend to keep a folder of "post-creation" scripts that set things up for us such as turning on and configuring database mail and creating a default set of users. Makes things a lot nicer when we need to do those sorts of updates.
I'd also recommend having a check if the user/login exists before trying to create it. I'd insert it into a temporary or permanent table all logins that were not auto-created due to existance and do a select on that table to report the non-created logins and that way you can see what failed due to prior existence.
It also seems a bit strange to me to do a complete copy-paste of users from one system to another. If the group of users is always the same, and since you are using AD for authentication, why not create a group in AD for that default set of users and then you only need to make 1 login and user instead of multiple for each new instance and presumably update each instance at each new hire?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 10, 2017 at 11:18 am
bmg002 - Tuesday, October 10, 2017 10:51 AMI know as the DBA where I work, if a script has errors when run, I run the rollback script and then contact the developer. We have a "No script should have errors when deployed on live" policy at work and it has saved us a few times. If the script says something like "--this will generate an error at times", then we require the developer to adjust the script to throw no errors before it gets onto the live system.I agree with Sue_H in that I'd recommend fixing the script. I'd also recommend having it sitting on a network share ready for re-run at instance creation. We tend to keep a folder of "post-creation" scripts that set things up for us such as turning on and configuring database mail and creating a default set of users. Makes things a lot nicer when we need to do those sorts of updates.
I'd also recommend having a check if the user/login exists before trying to create it. I'd insert it into a temporary or permanent table all logins that were not auto-created due to existance and do a select on that table to report the non-created logins and that way you can see what failed due to prior existence.
It also seems a bit strange to me to do a complete copy-paste of users from one system to another. If the group of users is always the same, and since you are using AD for authentication, why not create a group in AD for that default set of users and then you only need to make 1 login and user instead of multiple for each new instance and presumably update each instance at each new hire?
Different logins have different permissions. They are application service lpgins. A group does me no good.
Thanks for the advice
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply