| Author |
Topic |
|
TexMurphy
Starting Member
9 Posts |
 Posted - 2006-07-06 : 10:12:04
|
| I have set up a DTS to import data from an Access database to a server which is running SQL. It works alright if I execute the DTS, but if I set it to run as a scheduled task, it tells me that the path to the access db is not valid, and that I should make sure the path name is spelled correctly and that I am connected to the server on which the file resides... so obviously it's not a spelling issue (since when I execute it, it works), but a security issue. Anyone have any ideas?Thanks a lot,Tex |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-06 : 11:00:32
|
| When you execute it manually are you on the server or a client machine.dts is a client application so will execute on the client. When you schedule it the client is the server.It will probably be executing as the sql server sevice account so that will need to have the drive mapped and permissions granted.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
TexMurphy
Starting Member
9 Posts |
Posted - 2006-07-06 : 11:09:59
|
| I am RDPed onto the server. I'm not sure if it matters, but the access database refers to another access database (with the fields I am using). That second database is on the same drive as the other one. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-06 : 11:16:12
|
| What happens if you log in to the server as the sql server service account?Can you see the access database?Does the package run ok?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
TexMurphy
Starting Member
9 Posts |
Posted - 2006-07-06 : 11:24:09
|
| I'm logged in as domain admin, and I'm not really sure what you mean when you speak of the 'SQL server service account' (sorry, I'm a bit of a noob working on an SQL that already exists at my work place) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-06 : 11:44:06
|
quote:
Originally posted by TexMurphy
I'm logged in as domain admin, and I'm not really sure what you mean when you speak of the 'SQL server service account' (sorry, I'm a bit of a noob working on an SQL that already exists at my work place)
Since DTS is a client application, when you are connected to your server using Entperise Manager and you execute a DTS package that package will run under your user account. This will be the account that you logged into your server with - your "TexMurphy" account.However, when you schedule a DTS package to run, it no longer knows about the "TexMurphy" account. It will run under the context of the account that kicked off the DTS package. This is will be the account that SQL Server runs under - which is referred to as the SQL Server service account.You have to make sure tha the SQL Service account has access to drives where your access database lives, otherwise it will not be able to find the access db and your DTS job will fail. An easy way to test this is to login to your server using the SQL Server service account. Then you manually execute the DTS package and see if it works. THis way you are testing the permissions of that account and not your "TexMurphy" account.-ec |
|
|
|
TexMurphy
Starting Member
9 Posts |
Posted - 2006-07-07 : 05:12:04
|
| Hey there, is the account standard, since we have one called 'sa' that looks like it might be the one, but it's for SQL and not Active Directory, and therefore I can't logon using that. If I created an Active Directory account under the same name, would that work? |
|
|
|
TexMurphy
Starting Member
9 Posts |
Posted - 2006-07-07 : 06:12:55
|
| Hmmm, okay, so creating the active directory didn't seem to do that, since I am logged into that account, and I can run the DTS package (but scheduling still will not work), it says it could not lock the file, referring to the Access database on the other drive. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-07-07 : 07:35:24
|
| The scheduled package will run in the context of whatever user is the service account for the sql server agent. You can get this information in enterprise manager by right-clicking on sql server agent. If it's set to local system then you can't access remote drives, you'd need to change it to a domain account with permission on the remote folder.Also, don't use mapped drives, use unc paths.-------Moo. :) |
|
|
|
TexMurphy
Starting Member
9 Posts |
Posted - 2006-07-07 : 08:06:16
|
| Well, to Mr Mist and all those who helped me, I say a big 'thankyou' because it is now working. Thanks a lot! |
|
|
|
|
DTS permissions and schedules