March 31, 2008 at 12:31 pm
All of the above has been done. I'm trying to get the password for the group windows account verified. I'm hoping thats it....
¤ §unshine ¤
March 31, 2008 at 12:43 pm
That's easy to test. From a command prompt:
runas /user:**domain\user** cmd
Where **domain\user** is replaced by the Windows user account you were given. When prompted for the password, enter what you have. If a new command prompt opens up, it's correct.
K. Brian Kelley
@kbriankelley
April 1, 2008 at 2:20 am
The reason it does not work is because you have made a mistake in your setup.
The credential you set up for xp_cmdshell must have a specific name: ##xp_cmdshell_proxy_account##. You can associate this credential with any Windows account. This must be an account, not a group, as you have to specify a password.
After you have set up the credential, you need to grant authority to execute xp_cmdshell to whatever users need to run it. When they call xp_cmdshell, it will run under the authority given in the credential.
It is worth looking in BOL for more details of how this works. Another good source of information about proxies is the SQLServerFindBuild Reference document, as this gives some suggestions on how to deal with the security implications.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 1, 2008 at 7:03 am
Alright! Let me try this! Thank you!
¤ §unshine ¤
April 7, 2008 at 11:37 am
I've been going through the sql server fine build (great doc by the way). And I get this error when trying to add the user as a principal in the proxy.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Alter failed for ProxyAccount Name of _Credential'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Proxy " domain\user_SQL_xp" is not a valid Windows user. (Microsoft SQL Server, Error: 14529)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3159&EvtSrc=MSSQLServer&EvtID=14529&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
¤ §unshine ¤
April 7, 2008 at 11:56 am
Try to log on to a system with that username / password combination. Verify the combination you were given is valid.
K. Brian Kelley
@kbriankelley
April 8, 2008 at 2:40 am
I agree with K Brian Kelly. The most likely cause of the error message is that SQL Server cannot verify that the account exists. Check that the domain name, user name and password are valid.
The SQL Server service account must be in a domain that has a trust set up with the domain for the account you are using for the credential. If SQL is running as local system or using a local account and the proxy account is in a AD domain, SQL will not have the access needed to verify that the account exists in Windows.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 8, 2008 at 11:45 am
Now I get this error logging into sql server with my windows login. It is part of the domain otherwise it would not let me add myself as a sql server windows authenticated login.
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.
And found this.
ERROR_LOGON_TYPE_NOT_GRANTED
1385
0x569
Logon failure: the user has not been granted the requested logon type at this computer.
What do I request from network group? What do they need to add to my account?
¤ §unshine ¤
April 9, 2008 at 2:31 am
Almost certainly the account needs the 'logon as a batch job' right.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 9, 2008 at 7:19 pm
sunshine (4/8/2008)
Now I get this error logging into sql server with my windows login. It is part of the domain otherwise it would not let me add myself as a sql server windows authenticated login.Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.
And found this.
ERROR_LOGON_TYPE_NOT_GRANTED
1385
0x569
Logon failure: the user has not been granted the requested logon type at this computer.
What do I request from network group? What do they need to add to my account?
Your network group needs to look in the Security event log on the server. If they've configured auditing correctly, they will see an Audit Failure event record which corresponds to the user account. The Logon Type will be specified. That can be cross-referenced here:
That will tell you exactly what rights are needed, which can be modified in the Local Security Policy or by GPO. Ed's probably correct in that it only needs log on as a batch job rights. However, the only way to be sure is to check the logs.
K. Brian Kelley
@kbriankelley
April 10, 2008 at 7:15 am
Thank you guys! I'm having them look at it today. :w00t:
¤ §unshine ¤
April 14, 2008 at 11:49 am
that was it! It is now working smoooooothly.. thank you so much for your assistance and expertisse! 😀
¤ §unshine ¤
April 30, 2008 at 3:20 am
Hi, I have discovered a very strange problem in MS SQL Server 2005. When we reset the credentials of
"##xp_cmdshell_proxy_account##" through the command
EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0' it works always but when you try doing it from Enterprise manager it works never.
In fact in my siatuation the ##xp_cmdshell_proxy_account## was already running under an accound which had sysadmin privilages on sql server and also Administrative privilages on OS but when I tried running it through another user which was a non sysadmin, the xp_cmdshell failed with error code 1385. Here is the step I took
1) In sql server server property I enabled the proxy settings under security tab.
2) enabled xp_cmdshell for execution by using sp_configure and reconfigure. current value ots showing fot xp_cmdshell is 1 for all except min value.
3) created one user called 'test' and assigned it sys schema for master database and dbo for other user databaes.
4) I logged in to sql server with this account and try executing exec master..xp_cmdshell 'dir'
5) this command failed with error code 1385.
I tired it by creating other proxies also but no luck. When I resetting the sp_xp_cmdshell_proxy_account through command line it works inone shot.
actually I have a situation where I can not reset the sp_xp_cmdshell_proxy_account from command line as it reveals the password in clear.
can some one tell e if its a Bug in sql server 2005.
April 30, 2008 at 3:31 am
Vishal,
You need to grant execute rights on xp_cmdshell to user Test.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 30, 2008 at 3:35 am
user 'test' already has exeute rights on xp_cmdshell.
amazing thing is that, under same rights if i reset the credentials through query working but not through Enterprise manager.
?!?
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply