March 4, 2008 at 9:11 am
Hi, i created a job to execute a stored on a sqlserver2005 db retrieving data from a remote msaccesss db.
When i run the job i get this error (in italian):
03/04/2008 16:55:23,provaImportPatenti,Error,1,PREFNO,provaImportPatenti,importPatenti,,Executed as user: NT AUTHORITY\SYSTEM. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti" returned message ""Z:\patenti 2006.mdb" non è un percorso valido. Assicurarsi che il nome del percorso sia corretto e di essere collegati al server in cui si trova il file.". [SQLSTATE 01000] (Error 7412). The step failed.,00:00:00,16,7412,,,,0
I think is a matter of permission, but i don't know how to solve it.
The remote db has no password
On the linked server i set "sa" as local login and "impersonate".
Do i have to change somthing?
thnks
March 4, 2008 at 10:15 am
How about to change the execution account to others, such as local account?
March 4, 2008 at 10:45 am
If my italian isn't entirely shot - it's stating that the path isn't correct. Meaning - it doesn't understand what "Z:" is under the credentials executing the query.
Try changing the Z: to the UNC path that it maps to instead.
in other words, something looking like: "\\servername\sharename\patenti 2006.MDB"
If you still get an error, then it will be a permissions error. that means that the SQL Server service account wouldn't have access to that share (the account used to start up the SQL service), so you'd need to give it access to that remote share.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 11:35 am
Matt Miller (3/4/2008)
If my italian isn't entirely shot - it's stating that the path isn't correct. Meaning - it doesn't understand what "Z:" is under the credentials executing the query.Try changing the Z: to the UNC path that it maps to instead.
in other words, something looking like: "\\servername\sharename\patenti 2006.MDB"
If you still get an error, then it will be a permissions error. that means that the SQL Server service account wouldn't have access to that share (the account used to start up the SQL service), so you'd need to give it access to that remote share.
I would agree. Keep in mind that if you run a job from the job queue you will not have access to any mapped drives you have when you log in. This is a common problem. Another common problem is authentication. I found that I get myself into trouble if I try to rely on integrated security. If you use standard security (always supplying a userid/password) a bulk of your problems go away.
Good luck.
Report back your findings.
Kurt
DBA
RHWI, Inc
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 5, 2008 at 1:05 am
Hi, thanks for your posts.
First of all i deleted my linked server and executed these lines of code to add another one :
EXEC sp_addlinkedserver
@server = N'provaPatenti',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'\\server2\patenti\patenti 2006.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'provaPatenti',
@useself = N'false',
@rmtuser = N'Admin',
@rmtpassword = ''
GO
When i run a query on it it works well.
When i execute the job (that simply call the same query tested before) i get a different error (can't open file):
03/05/2008 08:43:15,provaImport,Error,1,PREFNO,provaImport,importPatenti,,Executed as user: NT AUTHORITY\SYSTEM. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti" returned message "Il modulo di gestione di database Microsoft Jet non è in grado di aprire il file '\\server2\patenti\Patenti 2003.mdb'. Il file è già aperto con accesso esclusivo da un altro utente o è necessario disporre dell'autorizzazione per visualizzare i dati.". [SQLSTATE 01000] (Error 7412). The step failed.,00:00:01,16,7412,,,,0
At this point it must be a permission problem.
What i didn't say before, and probably is important, the "patenti" folder on the server requires a username and a password to access it on the intranet. If the connection is not established before, even the call to the query out of the job fails (the job fails anyway either the connection is established or not).
Thank you very much.
March 5, 2008 at 5:09 am
SQL ORACLE (3/4/2008)
How about to change the execution account to others, such as local account?
Try this.
1. Open the SQL Server Configuration Manager.
2. For the SQL Server Agent process, change the 'Log On As' from Local System to a local account.
March 5, 2008 at 7:19 am
i changed the sql server agent log on "this account". but nothing changed.
Which account do i have to specify in this context? any of the local machine (server) ?
I created an account with the same username and password of the account nedded to access the remote folder where the access db is located. But i get the same error.
Thanks
March 5, 2008 at 7:20 am
Liuc -
you're correct - you now need to get permissions to that network folder for the account that is being used to start SQL Server. You may unfortunately need to change to a domain account (from a local account) in order to do so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 6, 2008 at 7:43 am
I agree with Matt. A Domain Account that executes (and owns) the Job, is configured to use the Linked Server and has access to the MDB file as well as the Folder where the MDB file is stored should make everything work. Keep us posted.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
March 6, 2008 at 8:06 am
Another thing to think about is this: Access has a default login of 'Admin' with no pwd. Try that combination for the security in your linked server and try again.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 10:36 am
Ok now it works.
I setup the sql agent access method on "This account" using the same account used to access the remote folder and the job works giving this account (on the remote server) the necessary permission to access the database.
Another thing i'd like to ask you, do i have to change the SqlServer process in the same way in order to execute a query (not a job) against the remote database?
March 6, 2008 at 2:35 pm
First - I'm assuming this is 32bit SQL Server as there is no Jet driver for 64 bit sql server...so linked server directly would not work in 64bit.
1 - Users who are accessing sql must have Modify permissions in the sql service accounts Temp folder per this link:
2 - SQL Server must be running under a domain account and account must be set in AD to Trust for Delegation
3 - SQL service account needs to have modify access to the folder where the mdb resides. I don't know if modify is need for other external file type linked servers but access needs it since an ldb file is created/deleted.
4 - SQL Server must have a SPN to allow kerberos to work. This might only be needed if using integrated security to connecto to sql to and in tern access the linked mdb.....but we already had the SPN set up.
With all of the above set it should work regardless of users connecting to sql via integrated security or a sql login.
March 6, 2008 at 5:36 pm
liuc (3/6/2008)
Ok now it works.I setup the sql agent access method on "This account" using the same account used to access the remote folder and the job works giving this account (on the remote server) the necessary permission to access the database.
Another thing i'd like to ask you, do i have to change the SqlServer process in the same way in order to execute a query (not a job) against the remote database?
In short - yes - very likely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2010 at 3:32 pm
brad.welch (3/6/2008)
First - I'm assuming this is 32bit SQL Server as there is no Jet driver for 64 bit sql server...so linked server directly would not work in 64bit.
Interesting, I failed trying to get it to work on a SQL Sever 2008 instance (32bit) with the following error.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYLINKEDSERVERNAME" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServerName".
So I created a linked server on SQL Server 2005 instance (32bit) and it worked.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply