 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Linked server to Access database
I need some help getting a linked server working to an Access database from SQL Server 2005. First off, here is the error I'm getting ======================= Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYACCESSMDB" OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linker server "MYACCESSMDB" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) ======================= Now, the facts and what I've done so far. The Access mdb is on a network share and, for testing purposes, Everyone has full control to the share. The mdb is not secure...so anyone with Access or Jet can open it. I set up a linked server under SQL 2005 developer edition, which is installed on my workstation and the linked server works just as I hope it would: From a SQL Server query I can see tables and data in the Access database. This local SQL Server runs under a local system account and I am SA in SQL and an admin on the box, and again the mdb is on a share with full control to everyone in the domain. I then ran the same linked server script (see below) on SQL 2005 running on a server in our domain. This SQL Server runs under a domain account, I am SA in SQL. When I execute the script I get the above "Unspecified error". Here is the linked server script I ran (which is based on the linked server template). EXEC sp_addlinkedserver @server = N'MYACCESSMDB', @provider = N'Microsoft.Jet.OLEDB.4.0', @srvproduct = N'OLE DB Provider for Jet', @datasrc = N'\\server\folder\myaccess.mdb' GO -- Set up login mapping using current user's security context EXEC sp_addlinkedsrvlogin @rmtsrvname = N'MYACCESSMDB', @useself = N'TRUE', @locallogin = NULL, @rmtuser = N'Admin', @ rmtpassword = NULL GO -- List the tables on the linked server EXEC sp_tables_ex N'MYACCESSMDB' GO It will not work with the service account running under local system. You need to set it up as a domain account.  SignatureJason Massie www: http://statisticsio.com rss: http://feeds.feedburner.com/statisticsio >I need some help getting a linked server working to an Access database from >SQL Server 2005. [quoted text clipped - 48 lines] > EXEC sp_tables_ex N'MYACCESSMDB' > GO His issue seems that he could access the database when his SQL Server 2005 ran with Local System while could not access the Access database with a domain account. Hi Brad, Your T-SQL statement has no problem. I also performed a test and it worked fine. I recommend that you check the following and let me know the result: 1. Log on your Windows with the domain account which is your SQL Server 2005's service account, and then manually type \\server\folder\myaccess.mdb to see if you can open the file. 2. In SQL Server Management Studio, expand "Server Objects->Linked Servers->Providers" to see if there is a provider named Microsoft.Jet.OLEDB.4.0. 3. Try using OPENDATASOURCE to access your Access database. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Thank you for the reply. You are correct that it worked locally but not with the domain account on the server. #1 I logged on as the domain account the service runs under and it does have access to the share and can open the mdb in access. Though I wonder if the sql service on the server needs to be restarted for SQL to see the share permissions granted (our dba is out today so I will have to wait to try that) #2 Yes, I there is a Microsoft Jet OLEDB 4.0 provider. #3 I tried OpenDataSource and OpenRowSet. First I had to enable Ad Hoc Distributed Queries. After that both returned the error OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". I found the link below while doing a google search. It seems to imply that it works locally is because I am an administrator on my pc, but I am not an admin on the server. http://blogs.msdn.com/spike/pages/ole-db-provider-microsoft-jet-oledb-4-0-for-li nked-server-null-returned-message-unspecified-error-msg-7303-level-16-state-1-li ne-1.aspx Brad > His issue seems that he could access the database when his SQL Server 2005 > ran with Local System while could not access the Access database with a [quoted text clipped - 21 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== See below. Per the link I found I also had myself set up as an administrator on the server but that did not resolve the problem. > Thank you for the reply. > You are correct that it worked locally but not with the domain account on the server. [quoted text clipped - 43 lines] >> This posting is provided "AS IS" with no warranties, and confers no rights. >> ====================================================== Hi Brad, Thank you for your response. According to the article, what is the result if you manually assign read and write permissions to your SQL Server service account on the Temp folder "C:\DOCUME~1\<SQL Server service account name>\LOCALS~1\Temp" on your server? If this issue persists, could you please restart your SQL Server service to see if it helps? Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Hi Brad, I am interested in this issue. Could you please let me know the result of suggesstions? Please feel free to let me know if you have any questions or concerns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Our dba is out sick so I'll have to wait until he returns to look into this. > Hi Brad, > Thank you for your response. [quoted text clipped - 17 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== Hi Brad, Thank you for letting me know this. Please feel free to post back when your DBA returns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== What we've found so far. We tried this with the Access mdb in a folder on the sql server and the information at this link (which I included previously) is correct. http://blogs.msdn.com/spike/pages/ole-db-provider-microsoft-jet-oledb-4-0-for-li nked-server-null-returned-message-unspecified-error-msg-7303-level-16-state-1-li ne-1.aspx The user executing openrowset or selecting against a linked server does need read/write permissions on the server to the SQL Exec's Temp folder i.e. if SQL Server runs under an account named SQLEXEC and my domain account is Brad, then Brad needs read/write on the sql server to C:\Documents and Settings\SQLEXEC\Local Settings\Temp. In addition to this the user who is executing the sql must have permissions to the folder where the mdb is located. And this all works only if using integrated security....which was disappointing as we use SQL logins for our web applications for the sake of connection pooling. And finally, we have not been able to get this to work when the mdb is on a network share on another server even though we verified that the SQL Exec account has access to the share and can open the mdb (SQL Exec is a domain account so we logged onto a workstation as that account and could open the mdb) > Hi Brad, > Thank you for letting me know this. Please feel free to post back when your [quoted text clipped - 12 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== Hi Brad, Thanks for your response. I think that if you can ensure that your SQL Server service account has permission to access (read/write) your remote Access file. You can still use SQL logins via OPENDATASOURCE. I performed a test at my side and it worked fine. For example: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','data source=''\\charles-2k3\sharefolder\sales.mdb''')...Customer To view and change your SQL Server service account, you can open Services from Control Panel, double click your SQL Server service and switch to the Log On tab. In this case, you need not specify a domain account in your query request. Please feel free to let me know if you have any other questions or concerns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== We found the problem. The domain account that sql runs under was not set to trust for delegation in AD. Once that was set linked server worked against a network share. 1 - Users must have access to the sql service accounts Temp folder per the link I sent before http://blogs.msdn.com/spike/pages/ole-db-provider-microsoft-jet-oledb-4-0-for-li nked-server-null-returned-message-unspecified-error-msg-7303-level-16-state-1-li ne-1.aspx 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. If any of the above are not done it will fail What is also interesting is that if sp_addlinkedsrvlogin parameter @useself = TRUE, windows authentication is required for connection to sql for the linked server query: if you use a sql login the linked server query will fail. If @useself = FALSE a query with a sql login will work. I am assuming that in this instance the parameter is determining if users authenticaiton will be use to connect to the mdb. TRUE = use users credentials via kerberos, FALSE = use the sql service accounts credentials. That's just a guess based on the results. Some other links I found useful http://support.microsoft.com/kb/319723 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/secu rity/tkerbdel.mspx I don't know if you can pass this on to anyone but a nice to have would be for this to all be documented in one place, kb or even the SQL Help, versus having to google to piece this all together. Thanks Brad We found another problem. This works only in a 32 bit sql server. There does not appear to be a 64 bit Jet driver for x64 SQL Server and Jet isn't even a linked server option on 64 bit MS SQL. Below is from earlier post, before the x64 issue =================================== We found the problem. The domain account that sql runs under was not set to trust for delegation in AD. Once that was set linked server worked against a network share. 1 - Users must have access to the sql service accounts Temp folder per the link I sent before http://blogs.msdn.com/spike/pages/ole-db-provider-microsoft-jet-oledb-4-0-for-li nked-server-null-returned-message-unspecified-error-msg-7303-level-16-state-1-li ne-1.aspx 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. If any of the above are not done it will fail What is also interesting is that if sp_addlinkedsrvlogin parameter @useself = TRUE, windows authentication is required for connection to sql for the linked server query: if you use a sql login the linked server query will fail. If @useself = FALSE a query with a sql login will work. I am assuming that in this instance the parameter is determining if users authenticaiton will be use to connect to the mdb. TRUE = use users credentials via kerberos, FALSE = use the sql service accounts credentials. That's just a guess based on the results. Some other links I found useful http://support.microsoft.com/kb/319723 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/secu rity/tkerbdel.mspx I don't know if you can pass this on to anyone but a nice to have would be for this to all be documented in one place, kb or even the SQL Help, versus having to google to piece this all together. Thanks > Hi Brad, > Thanks for your response. [quoted text clipped - 24 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== Hi, Thanks for your response. It is true that currently there is no 64 bit Jet OLE DB provider on a 64 bit system but only 32 bit Jet OLE DB provider exists, so that you can only use it from a 32 bit application. You can refer to this forum post for more information and see if the workaround (having the 64 bit SQL Server instance link to a 32 bit SQL Server instance which connect to Access database) works for you: How to support interface with MS Access databases in 64-bit applications? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=479203&SiteID=1 Regarding the parameter @useself of sp_addlinkedsrvlogin, we can see the following description: ---------------------------------------------------------------------------- ---------------------------------------------- A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. ---------------------------------------------------------------------------- ---------------------------------------------- According to it, your understanding was correct. Regarding document the solution/workaround for this issue, we will internally suggest the product team document this. However I also recommend that you give Microsoft feedback via https://connect.microsoft.com/sql since you may get email notifications when the product team respond you. Please feel free to let me know if you have any other questions or concerns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Thanks for the info. I though of the very same thing and tried it out. It works but it is a funky way to get there. Brad > Hi, > Thanks for your response. [quoted text clipped - 40 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== Hi Brad, Thanks for your response. I do understand that this may bring you some inconvenience. We have also internally reported this issue to the product team and hope that it will be fixed in the next release of SQL Server. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Hi Brad, Just check with you to see if you have any further questions or concerns on this issue. We appreciate your any feedback. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.