November 5, 2015 at 6:14 am
Brandie Tarvin (11/5/2015)
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.
First of all, I'm glad you have it working. However, there are still a couple of things to consider. First of all, don't forget to revoke that CONTROL SERVER permission you granted earlier. 😉
I thought you were connecting as yourself (a user with sysadmin privs) to create the procedure. In that case, you should have been able to create the procedure WITH EXECUTE AS OWNER and then granted EXECUTE on the procedure to the login used by the web site. If you were connecting as the db_owner instead of yourself, then that explains why it didn't work. If you were connecting as yourself and have sysadmin, then I'm at a complete loss as to what went wrong.
Personally, I don't like the idea of having EXECUTE on xp_cmdshell specifically granted to a login - any login, but especially a SQL login instead of a Windows login. I think it should be reserved for DBAs. I consider it dangerous because you can't predict now how that login is going to be used in the future and you don't want a bunch of different applications having the ability to run xp_cmdshell. I'm certainly not against xp_cmdshell...far from it, in fact. However, I do think that it should be controlled.
Now that you have it working and know how you did it, are you open to trying it the other way? I may be being overly cautious for your situation, but I don't want to see it come back and bite you down the road.
November 5, 2015 at 9:15 am
Ed Wagner (11/5/2015)
First of all, don't forget to revoke that CONTROL SERVER permission you granted earlier. 😉
Did that as soon as I figured out it didn't work.
I thought you were connecting as yourself (a user with sysadmin privs) to create the procedure. In that case, you should have been able to create the procedure WITH EXECUTE AS OWNER and then granted EXECUTE on the procedure to the login used by the web site. If you were connecting as the db_owner instead of yourself, then that explains why it didn't work. If you were connecting as yourself and have sysadmin, then I'm at a complete loss as to what went wrong.
I was connected as myself with sysadmin access. I even went so far (the second and third times) to run SSMS as an administrator in case that was the issue. Still the same problem. ISTR that we ran into this issue before and had to have an individual "real user" domain login given db_owner to the database and actually owning dbo in order to get impersonate to work, but that was back in 2005.
Personally, I don't like the idea of having EXECUTE on xp_cmdshell specifically granted to a login - any login, but especially a SQL login instead of a Windows login.
It's not a SQL Login specifically. It's a plain vanilla domain user account created specifically for this process. As is the SQL Server Service account.
Now that you have it working and know how you did it, are you open to trying it the other way? I may be being overly cautious for your situation, but I don't want to see it come back and bite you down the road.
The process is running under the service account. Setting up a proxy requires much code change in the webpage app. Not that I'm objecting to change. Just pointing out that redoing is not a simple fix. So if you have other suggestions, please let me know. I'll add them to the list of things to try.
November 5, 2015 at 9:25 am
Brandie Tarvin (11/5/2015)
Ed Wagner (11/5/2015)
First of all, don't forget to revoke that CONTROL SERVER permission you granted earlier. 😉Did that as soon as I figured out it didn't work.
I figured you did. It just didn't want to see you forget about it and have the #HeadDesk syndrome later in life.
Brandie Tarvin (11/5/2015)
Personally, I don't like the idea of having EXECUTE on xp_cmdshell specifically granted to a login - any login, but especially a SQL login instead of a Windows login.
It's not a SQL Login specifically. It's a plain vanilla domain user account created specifically for this process. As is the SQL Server Service account.
I'm especially glad to hear this.
Brandie Tarvin (11/5/2015)
The process is running under the service account. Setting up a proxy requires much code change in the webpage app. Not that I'm objecting to change. Just pointing out that redoing is not a simple fix. So if you have other suggestions, please let me know. I'll add them to the list of things to try.
I'm also glad to hear this. Setting up a proxy is pretty simple to do, but it is't simple at all if you already have things that could break.
All in all, it sounds like you've got a working approach. Like I said, I just didn't want something to remain unsaid and have you run into problems later.
November 5, 2015 at 9:52 am
And I appreciate the double-check. Keeps me honest. @=)
November 5, 2015 at 10:11 am
Glad to see that you got it working. Permit me, however, to point out that the solution was exactly what I was telling you. 🙂
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 5, 2015 at 10:14 am
Erland Sommarskog (11/5/2015)
Glad to see that you got it working. Permit me, however, to point out that the solution was exactly what I was telling you. 🙂
Um, not quite. Using the proc didn't work. I had to find a different way to create the credential.
July 20, 2018 at 9:17 am
This still doesn't address why you got the LogonUserW failed error. I recently experienced this error. The reason is that the proxy account did not have the correct Windows permissions. At minimum it needs "Log on as a batch job" on the Windows side. As far as I have seen, it does not need any permissions on the SQL Server side.
July 23, 2018 at 3:44 am
frobins - Friday, July 20, 2018 9:17 AMThis still doesn't address why you got the LogonUserW failed error. I recently experienced this error. The reason is that the proxy account did not have the correct Windows permissions. At minimum it needs "Log on as a batch job" on the Windows side. As far as I have seen, it does not need any permissions on the SQL Server side.
In my case, it was a SQL permissions issue, not a Windows issue. We already had all the Windows permissions accounted for.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply