| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-08-03 : 13:05:49
|
| 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,Canada DBA |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-03 : 13:53:49
|
| The user that SQL Server runs under needs to have access to the network share for your DTS package to work properly.-ec |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-03 : 14:03:56
|
Hi,Thanks for your reply. Would you explain more. Should I ask our network admin to create a new account under windows 2000? Then add the user id in SQL-Server?quote:
Originally posted by eyechart
The user that SQL Server runs under needs to have access to the network share for your DTS package to work properly.-ec
Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-03 : 14:27:47
|
| Does your SQL server run under a domain account, or does it run under 'Local System'. You can determine this by looking in the services control panel.If you run under local system account you will definitely need to talk to your network admin and have an account created for you. That account then needs to be added to the server that you are running SQL Server on into the local administrators group. (actually, SQL Server doesn't need local admin, but this is the easiest way.)After that is done, you will need to change the accounts that SQL server uses to the new account that has been created for you. I believe all that needs to be done is to modify the entries in the services control panel to use a new account and then restart them.After that is done, just have your network admin give the account you run SQL Server with permissions to access the network share that the UNC path points to.-ec |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-03 : 14:38:02
|
| gunned down |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-03 : 14:52:45
|
I found the following info on Services control panel:MSSQL$BKUPEXEC LocalSystemMSSQLSERVER Domain\SQLAdminMSSQLSERVERADHelper LocalSystemSo, it seems SQL-Server run under domain account. What then?quote:
Originally posted by eyechart
Does your SQL server run under a domain account, or does it run under 'Local System'. You can determine this by looking in the services control panel....-ec
Canada DBA |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-03 : 15:07:55
|
| SQLAdmin needs access to the network location you are trying to reach.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-04 : 10:58:34
|
There was a Joker in Batman movie! quote:
Originally posted by Sitka
...It is a rich creamy color with a high ...
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-04 : 10:58:56
|
| 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. 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? Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-04 : 11:23:01
|
| 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 |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-04 : 14:30:31
|
| Why?Domain\SQLAdmin is a Windows login TYPE not valid as an entry in the QA login when the little radio button says SQL authentication. If you want to use QA as Domain\SQLAdmin; login to a Windows machine that has the client tools installed as Domain\SQLAdmin and choose Windows Authentication. QA connection inherits the Windows login and passes it to SQL, The only other option is to use a standard login type. Visable here EM>Security>Logins>type columnIt is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-04 : 18:21:42
|
I was confused with your message as signature.Thank you for your reply. I'll try to use it. quote:
Originally posted by Sitka
Why?...
Canada DBA |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-05 : 08:51:40
|
| You could Google my signature and find out.I'm confused by your signature to. ;-)It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-05 : 09:07:10
|
I got it Sitka. Thanks for explanation. Now, I want to re-define my need and probably get a how to for that.I have a DTS package that have to grab a text from a file server and import it into a table on another server (called SQL server). Both servers are located on a LAN under a domain. This DTS is not a scheduled job. A SP will call it. I have domain\SQLAdmin 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. Based on what you have written: 1. I log in on the SQL server using SQLAdmin, 2. set the DTS to use windows authentication, 3. log out the server. Then I can call the DTS from my local computer. Does it work? FYI, at the moment, I have logged in the server and mapped a drive letter to the path that the text file exists and have locked the server's keyboard and monitor. But the most problem is that if someone reboot the server, then the DTS would fail until I do log in and mapping process. I want to prevent this process by using the UNC path. quote:
Originally posted by Sitka
Domain\SQLAdmin is a Windows login TYPE not valid as an entry in the QA login when the little radio button says SQL authentication. If you want to use QA as Domain\SQLAdmin; login to a Windows machine that has the client tools installed as Domain\SQLAdmin and choose Windows Authentication. QA connection inherits the Windows login and passes it to SQL, ...
Canada DBA |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-05 : 09:32:06
|
| The idea here is that MSSQLSERVICE and SQLAGENT are both set up to use Domain\SQLADMIN, remember the control panel>Services part.That makes the DTS package RUN under authoutrity INDEPENDENT of login. That's it, the SQL part of the it ends there.The other part is that the windows network security. The NETWORK login Domain\SQLADMIN needs the appropriate permissions on the network to use the UNC.Now there are some very subtle other things that can break how this works but most of them materializeif the Server has been upgraded from NT4 -> WIN2000 and has to do with group policy.At the simpliest level Domain\SQLADMIN is a regular user with "logon as service" rights on the local SQL machine.After that since it is as a service that user has full authority over the SQL install and all the little cool things it can do. >>>Then I can call the DTS from my local computer. Does it work? Don't try that first because you are introducing another layer of authentication that could be inherited or "token passed" behind the scenes. Move to a next logical step, "schedule" the DTS package to run, see if it works....then logout of the server. Come back after a while and see if it works.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-05 : 09:56:26
|
| I didn't clarify that HOW I will call the DTS from my computer. Here is the way that I call the DTS:exec @Ret=xp_cmdshell 'dtsrun /S ServerIP /U SQLAdmin /P password /N MyDTS'And what if I set the DTS to use windows authentication? I think this will satisfy both SQL and File Server security because SQL-Server switches to command shell as SQLAdmin with rights that can read from File Server and then runs the DTS which is using an id that already is authenticated. So it should work. What's your idea?Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-05 : 11:02:19
|
| 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?Canada DBA |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-05 : 13:51:02
|
| Yeah you are close no doubt but it comes down to the exact mechanism involved in the behind thescenes < because SQL-Server switches to command shell as SQLAdmin with rights > part. Simply my suggestion was to let in run "hands off" via a schedule first. If that works then you could log off the server and it would run again. INDEPENDENT of login. If that works "then" you could call exec from your machine. That is just how I would step through it.<Is it right to say that the DTS package ran correctly and it will work even if the server is logged off?>I don't know. Can't get a flow reading about it is not like being there.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-09 : 10:31:15
|
Thank you Sitka! I read your previous post and learnt what you mean. now, it is more senceable to me. I am going to try that suggestion, too.quote:
Originally posted by Sitka
Yeah you are close no doubt ...
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-08-10 : 13:20:10
|
| There is an application which is supposed to call a SP and that SP calls the DTS. I am going to find out if I can avoid of writing an admin username and password in that SP. The 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? I know 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,Canada DBA |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-08-10 : 14:37:38
|
| Lots of references to your additional requirements. Ex.http://www.tek-tips.com/gviewthread.cfm/pid/961/qid/860412 (thanks nr)...as well as this site.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
Next Page
|
Using UNC path