SQL Server Performance Forum – Threads Archive

Using UNC Path with login to the server

I am testing useage of UNC path in a DTS package. This DTS imports a text file into a table. The text file is located on File Server. I have set up a DTS package which uses UNC path to grab the text file from the File Server. While the first server can ‘see’ the File Server, the DTS works perfectly. Question is that if I reboot the first server and don’t login, then the server shouldn’t be able to ‘see’ the File Server. What happens to my DTS package? Does it fail? My test shows that it would fail if my machine is not able to access the File Server. Therefore, is it true that we can use UNC only when our machine or the server has at least read right on the File Server? How can I use UNC path without need to login the File Server? Regards, P.S. Moderators, Although this post refers to DTS but please leave it in Gerneral DBA Questions because it is UNC related than DTS.
CanadaDBA

You need to set read rights for the account executing the DTS pckage on the share on the server you connect to with the UNC path. Usually your SQL Server Agent account if you run it as a scheduled job using a sysadmin account. UNC paths are available without logging in on the server, you just need the correct rights. Note that the SQL Server Agent account can not be the Local System Account if you want to access resources outside the local server. Use a domain account or same name/password on both servers. Depending on domain setups you can give the Win NT Guest user read rights on the share. Then the DTS package (and anybody) might be able to access it directly. But it’s probably better to set it upp correctly by giving the SQL Server Agent account access to the share. More on DTS security:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q269074

Thank you Argyle. I have a domainSQLAdmin account. I have also a username that has access right to the File Server. I used it to map into File Server. I’ll check if I can use any of them to access the File Server and post the result. CanadaDBA

I have Domainsqladmin which is a domain user id. I use it for FTP to the server. Also, I was able to map to the server using this id. In SQL-Server EM I have the Domainsqladmin id as windows user under Security->logins. This id is Server Admin and Sys Admin in EM. It also have permissions to all my DBs. But I can not connect to the server using this id in QA. Even in EM I tried to use it but I was not able to connect. Why?
CanadaDBA

I tried to connect throu QA using the DomainSQLAdmin on the server itself but it faild. I used Windows Authentication and it connected successfuly. I noticed that the user name is DomainSQLAdmin. I don’t know why even on the server, I can not use this id to connect QA? And therefore, If I can not use this id to connect QA to SQL-Server, then I would not be able to use it for DTS package. Am I right? CanadaDBA

Not sure I’m following you. Have you given domainSQLAdmin access to the share on the file server? You need to set up a share on the file server, set read rights for the account before you can access it. Are you accessing the server through UNC path? Services like SQL Server can’t see mapped drives. Note that if you run the package as a scheduled job it will be the account that the SQL Server Agent is running under that connects (if the job owner is sysadmin). This might be different than domainSQLAdmin.

Argyle,
I have a DTS package that have to grab a text from a file server and import it into a table. Both servers are located on a LAN under a domain. This DTS is not a scheduled job. A SP will call it. I have domainSQLAdmin which is a domain user id and have access to both servers. This user id on SQL-Server is SysAdmin and ServerAdmin. It has DBO rights to all DBs. The question is that should I use domainSQLAdmin in DTS package connection properties? Should I set up this connection setting on the server itself or I can do it from my computer which has remotly access to the server. In another try, I tried to find out how can I use such a user id to connect to SQL-Server using QA. I couldn’t use domainSQLAdmin as a userid when QA was asking an id to connect to SQL-Server. I don’t know why. All replies are greatly appreciated. CanadaDBA

I set the DTS package to use Windows authentication. Then I called the DTS as: exec @Ret=xp_cmdshell ‘dtsrun /S ServerIP /U SQLAdmin /P password /N MyDTS’ And it worked! I am not sure I did right because my server still is logged on and mapped to the File server. So, the best way is to log out it and then run call the DTS again. This needs to follow a long process and approval to ask Net Admins to log out the server and I test my findings. What’s your idea? Is it right to say that the DTS package ran correctly and it will work even if the server is logged off? CanadaDBA

Unmap the drives and try again. This can be done from windows explorer. No need to logout to test it. As long as you refer to UNC paths in the dts package and not mapped driver letters it should work. Running xp_cmdshell is another issue though. If you run the SP with a user that is sysadmin it will already have the rights to execute xp_cmdshell. Then xp_cmdshell will run with the credentials of the sql server account that already have access to dtsrun.exe so it should be no problem. But if the user executing the SP with a user that is not a sysadmin it needs to be given acces to xp_cmdshell. When xp_cmdshell is run by a non sysadmin user, it’s executed with the credentials of the sql proxy account (an NT account). You need to configure this account (under server properties) and then give this account rights to execute dtsrun.exe as well.

Thank you Argyle! Like always, you pointed good ideas and remarks. I’ll keep them in my mind. Referring to your last line, how can I configure an account to give it the right to execute dtsrun.exe?

quote:Originally posted by Argyle …
When xp_cmdshell is run by a non sysadmin user, it’s executed with the credentials of the sql proxy account (an NT account). You need to configure this account (under server properties) and then give this account rights to execute dtsrun.exe as well.

CanadaDBA

Right click on the exe file, select the security tab, add the windows account in question and give it execute rights. The windows account will either be your sql service account or your sql proxy account depending on your configuration.

There is an application which is supposed to call this SP. That application uses a userID with limited rights. I am trying to find out a way to grant rights to that user. Using that userID, connected to QA and ran the shell comand: exec @I=master.dbo.xp_cmdshell ‘dtsrun /S 1.2.3.4 /E /N UNC_Import’ and got the following error: EXECUTE permission denied on object ‘xp_cmdshell’, database ‘master’, owner ‘dbo’. It seems I have to do two things:
1. Grant EXE right to run ‘xp_cmdshell’ to the user.
2. Grant execute right to run dtsrun.exe in windowes to the SQL-userId. Am I right? You have already told me about how to grant permission to a file in Windows environment. But I couldn’t find ‘my limited SQL-userid’ in the drop-down menu. The last thing, what about the xp_cmdshell execute grant? I am searching for this but you (or someone) may answer me sooner. Thanks,
CanadaDBA

Sql server logins are related to sql server only and has nothing to do with Windows. They can only be used with sql server. Windows accounts on the other hand can be used in windows and be granted access in sql server as well. So if you have a sql server login with limited rights you need to grant execute rights for this user to xp_cmdshell (done in sql server). When this user execute xp_cmdshell it will be run with the credentials of the sql server proxy account (unless your user is sysadmin and then it will be the sql server service account). This proxy account (which is a windows domain user) need to be configured in sql server (see BOL) and given rights to run dtsrun.exe and to any network shares you use.

]]>