Using UNC path

Question

Using UNC path

Farhad-138839

SSC Enthusiast

Points: 115
More actions
August 3, 2004 at 12:44 pm

#109022

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? I have heard that I can create a SQL-Server user id which has access right to the File Server and use that Id to run the DTS. But I don't know how.
I appreciate your replies.
Canada DBA,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Daljit S. Saini SSC Enthusiast Points: 189 More actions · Answer 1

What you can do is , run the SQL Server with domain account and give all the required rights on the folder on the file server to that domain account, with which the SQL Services has been started. In this way you will be able to access the folder.

Farhad-138839 SSC Enthusiast Points: 115 More actions · Answer 2

I have Domain\sqladmin 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 Domain\sqladmin id as windows user under Security->logins. This id is Server Admin and Sys Admin in EM. 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?

Canada DBA,

Farhad-138839 SSC Enthusiast Points: 115 More actions · Answer 3

I tried to connect throu QA using the Domain\SQLAdmin on the server itself but it faild. I used Windows Authentication and it connected successfuly. I noticed that the user name is Domain\SQLAdmin.

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?

Canada DBA,

Farhad-138839 SSC Enthusiast Points: 115 More actions · Answer 4

48 hours passed and no reply!!

It seems other forums are more active (like SQLTeam or SQL-Server-Performance) and responsible.

Anyway, I could solve my problem and post it here.

I set the DTS package to use Windows authentication. Then I called the DTS (using a Stored Procedure) as below:

exec @Ret=master.dbo.xp_cmdshell 'dtsrun /S ServerIP /U SQLAdmin /P password /N MyDTS'

This way, by calling the SP, it switches to command shell and runs the DTS with a domain name that has access to my File Server. That's it!

Canada DBA,