March 23, 2005 at 2:51 am
Hello
Our server has numerous dts packages. They were created using Enterprise Manager on the server itself, logged in using Windosw Authentication. The owner of these jobs appears as the Windows user who is logged onto the machine, and has a corresponding login (but no corresponding user) in this instance of SQL Server.
I am trying to convert or replace these packages with others doing the same jobs, but owned by a user (dtsuser) I have created for the purpose of running DTS packages, to which I plan to grant only the necessary permissions required for running the packages, rather than full sysadmin rights, which the Windows user account has.
I can make this work on packages which deal only with databases on this server, but some of the packages reference a MYSQL linked server. If I log into the SQL Server instance as dtsuser using SQL Server authentication from my machine, I can access this linked server using OPENQUERY. However, if I try to create or edit a DTS package and add a connection to this linked server, I cannot see it in the list of available user/system DSNs.
What do I have to do in order for dtsuser to see the linked server?
Thanks in advance for any light you can shed on this
Mark
March 23, 2005 at 5:04 am
Try adding the same connection to MySQL under the DTSUser account in ODBC connections (Can be found in Control Panel under Admin. Tasks). This should allow you to add the connection.
The problem I think is because the DSN was created under a different account and not the one you wish to use now...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 23, 2005 at 9:04 am
Thanks for replying. Even if this works, this sounds like the recipe for a future administrative nightmare. EG, someone changes the password on the MYSQL machine, changes it on the linked server as seen by the sysadmin account, then spends hours scratching their head as to why all the DTS jobs have failed.
Another solution which I may well try is to abstract the whole DTS work into stored procedures which use OPENQUERY/OPENROWSET, which dtsuser can access over the existing linked server without difficulty.
Cheers
Mark
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply