November 4, 2015 at 11:25 am
I'm pulling data from a NAS share and have recently run into the following error.
An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code : ‘1385’.
The domain on several of our logins changed from XYZ to ABC. Now we've been able to keep the SIDS (or so I've been told) on most of them. But we've had the occasional issue. This may or may not be one of them. Right now, I have a request in to our server team to check the current permissions on the share for the login in question. In the meantime, as I'm looking into this, I ran into an article about the problem not necessarily being about permissions but logon types.
What logon types (rights) would I need for xp_cmdshell to read and execute a directory on a NAS Share?
November 4, 2015 at 12:24 pm
Brandie Tarvin (11/4/2015)
I'm pulling data from a NAS share and have recently run into the following error.An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code : ‘1385’.
The domain on several of our logins changed from XYZ to ABC. Now we've been able to keep the SIDS (or so I've been told) on most of them. But we've had the occasional issue. This may or may not be one of them. Right now, I have a request in to our server team to check the current permissions on the share for the login in question. In the meantime, as I'm looking into this, I ran into an article about the problem not necessarily being about permissions but logon types.
What logon types (rights) would I need for xp_cmdshell to read and execute a directory on a NAS Share?
The login you're using has to have permission to do the xp_cmdshell. Hopefully, you're creating the procedure WITH EXECUTE AS OWNER instead of granting permission to the login.
If the command you're executing has to access any network resource, it needs to have permission to it. You know this part already. Since a SQL login doesn't exist in Windows, but only within SQL Server, it won't have permission to network resources. The Windows login (SQL login for an AD account) should be used and it has to have the appropriate AD permissions. Unless you have a proxy set up, the SQL Server service account will be the one it runs as. To verify the account the shell runs as, use this:
execute xp_cmdshell 'whoami';
If you're staying on the same domain, I don't see a problem. I have no experience with accessing a different domain, but it sounds like the domain was only renamed. I suppose the real question boils down to where the AD account exists that you're using to run the command shell.
Am I even close here?
November 4, 2015 at 12:30 pm
NET HELPMSG 1385 says:
Logon failure: the user has not been granted the requested logon type at this computer.
But if this was a permission issue on the NAS, I would not expect this error message. I would interpret the message it is the attempt to invoke xp_cmdshell that fails. It was a permission error on the NAS, you would just get that in the output from xp_cmdshell.
My guess is that you have a proxy account setup for xp_cmdshell, and this account has been affected by the domain shakeup.
To get the NAS out of the question just run a simple command like
EXEC xp_cmdshell 'ECHO "Hello!"'
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 4, 2015 at 1:10 pm
EDIT: YAY! I can post now! Except when I try to post the additional info, it craps out on me... Let me try without the code bit and see if that's the issue.
Okay, here's the additional info:
I know the issue is a SQL Server permission. I'm trying to figure out what permission. The login has permissions on the database with a proc that checks the NAS. I just gave the login sysadmin (for 2 seconds) and suddenly the webpage worked.
So I have db_owner permission in the database that this proc is housed in. I granted EXEC on schema "me" just to be sure that it could execute the proc. Then I even added db_datareader, db_datawriter, and CREATE TABLE permissions in TempDB to be sure. Then I granted EXECUTE and public to the master database.
Still getting the stupid error in the webpage.
This login has no server wide perms. Any thoughts on what permission I can give this login without granting it sysadmin access to run this code?
BTW: The server admin came back with "this login has full permissions to the NAS" before I tested the sysadmin permissions.
November 4, 2015 at 1:13 pm
Okay, so the problem is that I can't post the code for my stored procedure. Grrrrr.
See attached text file.
November 4, 2015 at 1:17 pm
No, it is not an SQL Server permission per se. The error comes from LoginUserW which is a Win32 routine. So fiddling with groups etc in SQL Server is not going to help.
Your information is incomplete, but it seems that you have a web application, but it is not clear to me how the web app logs in.
In any case, I would check whether there is a proxy account set up for xp_cmdshell, and review the settings for this account.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 4, 2015 at 1:51 pm
I'm not sure what Erland means by the LoginUserW, but if it worked when you gave the login sysadmin privs, then we might be onto something.
If it worked when you gave the login sysadmin privs, then you must have sysadmin privs. 😉
If that's the case, then try recreating the procedure and tell it to execute as you and grant execute on the procedure to the login being used by the site. Example:
CREATE PROCEDURE dbo.DoSomeFileStuff WITH EXECUTE AS OWNER
AS
BEGIN
--do your stuff here
END;
go
GRANT EXECUTE ON dbo.DoSomeFileStuff to [sql_login_used_by_the_site];
Then see if it works. If so, then you know it's the SQL login in use by the web application, which is a good. A login used by a web site should not have permission to shell out to the operating system. It can, however, have permission to run a procedure that does it. Of course, there will be performance implications while you want for the procedure to finish, but I'm sure you know that part already.
EDIT: Erland is exactly right. You need to know the user your shell is going to run as, be it a proxy account or not. Use the "whoami" command I posted earlier right from SSMS to determine who the user it's going to use to run.
November 4, 2015 at 2:19 pm
Brandie Tarvin (11/4/2015)
Okay, so the problem is that I can't post the code for my stored procedure. Grrrrr.See attached text file.
Perhaps it could be those " characters? I haven't used xp_cmdshell in a long while, but last time I used it that was a varchar so it needed single quotes, not double.
Tom
November 4, 2015 at 2:20 pm
Check out this article
November 4, 2015 at 2:27 pm
Ed Wagner (11/4/2015)
I'm not sure what Erland means by the LoginUserW, but if it worked when you gave the login sysadmin privs, then we might be onto something.
LoginUserW comes from the error message. It is a Win32API call. The reason it works with sysadmin is that with sysadmin, SQL Server does not try to use the proxy account, but xp_cmdshell runs under the credentials of the service account for SQL Server.
You can review the current proxy user for xp_cmdshell in sys.credentials. Look for the name ##xp_cmdshell_proxy_account##.
The best thing to sort out the issue is probably to drop the current proxy user with "sp_xp_cmdshell_proxy_account NULL", and then add the correct user with
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user', 'thesecretpassword'
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 5, 2015 at 4:45 am
Erland Sommarskog (11/4/2015)
NET HELPMSG 1385 says:Logon failure: the user has not been granted the requested logon type at this computer.
But if this was a permission issue on the NAS, I would not expect this error message. I would interpret the message it is the attempt to invoke xp_cmdshell that fails. It was a permission error on the NAS, you would just get that in the output from xp_cmdshell.
My guess is that you have a proxy account setup for xp_cmdshell, and this account has been affected by the domain shakeup.
To get the NAS out of the question just run a simple command like
EXEC xp_cmdshell 'ECHO "Hello!"'
We don't use proxy accounts, but yes, I've already determined the issue is a SQL Server permissions issue. (Catching up on this thread because I didn't have time to read it yesterday).
November 5, 2015 at 4:48 am
Ed Wagner (11/4/2015)
Hopefully, you're creating the procedure WITH EXECUTE AS OWNER instead of granting permission to the login.
I have not tried this yet. The login does need access to certain tables on the database (outside the xp_cmdshell) so it does indeed have user DB permissions. As far as tempDB and master perms, that was yesterday's attempt to fix the issue.
Am I even close here?
Hey, the extra perspective always helps. Like I said, was banging my head against a wall all morning yesterday. Kind of set me blind to other possibilities, if you know what I mean. :crazy:
November 5, 2015 at 5:04 am
Erland Sommarskog (11/4/2015)
Ed Wagner (11/4/2015)
I'm not sure what Erland means by the LoginUserW, but if it worked when you gave the login sysadmin privs, then we might be onto something.LoginUserW comes from the error message. It is a Win32API call. The reason it works with sysadmin is that with sysadmin, SQL Server does not try to use the proxy account, but xp_cmdshell runs under the credentials of the service account for SQL Server.
You can review the current proxy user for xp_cmdshell in sys.credentials. Look for the name ##xp_cmdshell_proxy_account##.
The best thing to sort out the issue is probably to drop the current proxy user with "sp_xp_cmdshell_proxy_account NULL", and then add the correct user with
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user', 'thesecretpassword'
Thank you, Erland. Now I've learned something new, which is always a good way to start the day.
November 5, 2015 at 5:34 am
Ed Wagner (11/4/2015)
If that's the case, then try recreating the procedure and tell it to execute as you and grant execute on the procedure to the login being used by the site. Example:
CREATE PROCEDURE dbo.DoSomeFileStuff WITH EXECUTE AS OWNERAS
Then see if it works.
Nope. Getting this error:
Cannot execute as the database principal because the principal "db_owner" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Now this login does have db_owner access to the user DB. I've tried per Jack's suggestion on this thread to grant CONTROL on master db. Also specifically granted EXECUTE on xp_cmdshell to the login, that didn't work.
Added my login to master and the user database. Granted myself db_owner. Tried using WITH EXECUTE AS with my login, still getting the "call to 'LogonUserW' error.
Use the "whoami" command I posted earlier right from SSMS to determine who the user it's going to use to run.
We don't use proxies.
"whoami" comes back with the SQL Server Service Account and NULL. I've tried adding the service account to the user db and the master db, given it specific EXECUTE to the xp_cmdshell proc. Used WITH EXECUTE AS <service account> on the proc.
Trying to clear the proxy accounts doesn't change the results of "whoami". EDIT: Also tried adding the service account to the proxy (even though it already shows when running the sp_xp_cmdshell_proxy_account and I get this error: "An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'."
Back to square one.
November 5, 2015 at 5:56 am
AAAAAAHHHHHH!
create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'
This let me create the credential for the service account without having to create a proxy account (which would require a lot of stuff having to be recoded).
Yes, I know. Granting the service account access to xp_cmdshell is definitely not the best solution. In the future I will push for using a different method of running this particular web page. But right now, it works. This webpage is an internal dashboard of jobs and processes that we monitor. No one outside IT has access to the page or the code behind the page.
YES. Problem solved. Thank you everyone for pointing me down the right path.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply