Strange problems with windows 2003 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Strange problems with windows 2003

I upgraded two of our sql servers over the weekend to new machines which run windows server 2003. Everything was transferred across ok, and the server processes are all running as the exact same domain accounts as they previously did.<br /><br />What im finding now, is we have some scripts which use bcp to export data to mapped drive letters. All of these now fail with the error ‘cannot open host file’. The drives are mapped and the server has full admin permissions over the shares and underlying directories.<br /><br />Also our scripts which perform various operations using UNC paths are also failing with the same error. The only difference I can see is that its windows 2003 instead of windows 2000 that they used to run on.<br /><br />Anyone had this problem? Its becoming quite urgent <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />
None at my end, have you tried to re-write those queries to export files to a mapped drives.
Check registry & Win Explorer whether the account has require privileges on that drive. As a workaround try creating the file locally and use a dos MOVE command to move the files to that location. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks satya. Not tried rewriting the queries as yet. Both the mapped drive method and UNC path method fail, so I think the problem is more of a network administration one, rather than implementation. Just tried getting bcp to export locally and then using xp_cmdshell to copy the file to the destination. xp_cmdshell returns the error Access Denied. I think the cmdshell is not running as the user i intend it to.
The machine is logged on as a domain user, which is the same account sql and sql agent run under.
ok, just issued a xp_cmdshell ‘whoami’ on 2003 and it returns ‘nt authoritysystem’
making progress kinda… wonder if theres anyway to specify what account cmdshell runs under.
I don’t think there is any difference between Win 2K and 2k3 for running XP_CMDSHELL,http://databasejournal.com/features/mssql/article.php/1580041 check whether this link is any help. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Not really, but thanks anyway <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />I dont have any problems running cmdshell. My problem is the behavior does appear to have changed… either that or I need to make configuration changes to the server.<br /><br />Historically I think it has always been true that xp_cmdshell would run either as the same account sql runs under (for sa users), or if the user isnt sa, then it would run as the sqlserver agent proxy account.<br /><br />Im trying to confirm this but I havent yet found a way to display the current username in a command prompt on windows 2000 <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> ‘whoami’ is new to 2003 it seems
DECLARE @cmdBody nvarchar(4000)
SELECT @cmdBody=N’SET’
EXEC master..xp_cmdshell @cmdBody
SET NOCOUNT OFF
(edited) output from the production server (win 2000)… USERPROFILE=C:Documents and Settingscapnetsql
(edited) Output from the new windows 2003 server USERPROFILE=C:Documents and SettingsLocalService
Both sql servers are setup to run under the same account.
If you’ve Win2K resource kit then you can download this WHOAMI.EXE program tool. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
After further exploring… On Windows 2003, it would appear that if the logged in user is a member of the system administrator role, when xp_cmdshell is launched it runs under the "nt authoritysystem" account, and NOT under the same account as sql server itself executes. This behavior has chnaged from windows 2000, but I would very much like someone else to confirm this using DECLARE @cmdBody nvarchar(4000)
SELECT @cmdBody=N’whoami’
EXEC master..xp_cmdshell @cmdBody This is VERY bad since you cannot really access network resources. When the logged in user is NOT a member of the system administrator role, the behavior of xp_cmdshell is the same as windows 2000, in that it will run as whatever account SQL Server Agent is running as. Fortunately, this is the behavior I need, since then xp_cmshell runs as a domain account, and my scripts start working again.
On win2k I have:
USERPROFILE=C:Documents and Settingssqlservice
whoami: win2kserversqlservice On win2k3 I have:
USERPROFILE=C:Documents and Settingssqlservice
whoami: win2k3serversqlservice Logged in as administrator on both servers and builtin administrator role exists in sql server. On winxp workstation I have:
USERPROFILE=C:Documents and SettingsLocalService
whoami: NT AUTHORITYSYSTEM On the workstation sql is not running with a seperate account and that seems to be your issue as well.
Interesting, thanks argyle. It did seem very odd for this to change. Do you recall any special windows 2003 settings you made (im thinking probably not!)? I didnt install our windows 2003 so I dont really know how its been configured too well yet. Cheers
It’s a standard install of win2k3 and a standard sql 2000 sp3 on top of that. The only issues I’ve had with win2k3 are that msdtc is locked down for network access by default.
]]>