January 6, 2007 at 4:38 am
to get Data from an Access-DB using "OPENROWSET"
Works fine, if mdb-File is on C:\Drive
error occurs, if file is on an other machine
-- Using SQL-Server Authentification (MUST!)
SELECT
TestField1 as t1_from_C_Drive From OpenRowset('Microsoft.Jet.OLEDB.4.0',';Database=C:\temp\Test.mdb;',
'SELECT * from T_Test') as b
--works fine==>mdb-File is on C:\Drive
SELECT
TestField1 as t1_from_Other_PC From OpenRowset('Microsoft.Jet.OLEDB.4.0',
';Database=\\Ohnemus-server\für-alle\Test.mdb;',
'SELECT * from T_Test') as c
--doesn't work==>same mdb-File as above is on an other machine
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
:-)) Philipp
January 6, 2007 at 9:00 am
Do you (and the server) and read access to that file on the remote server?
January 6, 2007 at 11:07 am
Use this to see if the server can see the file on the network :
Declare @out int
EXEC master.dbo.xp_fileexist 'C:\demo.notexists', @out output
SELECT @out
EXEC master.dbo.xp_fileexist 'C:\AUTOEXEC.BAT', @out output
SELECT @out
replace the autoexec part with the network filename.
January 7, 2007 at 2:03 am
starting with windows or SQL-Server authentification Test C:\Drive:
SELECT
* From OpenRowset('Microsoft.Jet.OLEDB.4.0',
';Database=C:\Test.mdb;',
'SELECT * from T_Test') as c
==> works fine
EXEC
master.dbo.xp_fileexist 'C:\Test.mdb', @out output
SELECT @out
==> works fine
starting with windows authentification Test Y:\Drive on network:
SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0',
';Database=Y:\Test.mdb;',
'SELECT * from T_Test') as c
==> works fine
EXEC
master.dbo.xp_fileexist 'Y:\Test.mdb', @out output
SELECT @out
==> file NOT found!!!!!!!
starting with SQL Server authentification Test Y:\Drive on network:
SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0',
';Database=Y:\Test.mdb;',
'SELECT * from T_Test') as c
==> does'nt work
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
There is NO PROBLEM to start Y:\Test.mdb directly with MS-Access!
EXEC
master.dbo.xp_fileexist 'Y:\Test.mdb', @out output
SELECT @out
==> file NOT found!!!!!!!
January 7, 2007 at 7:48 am
Thanx for the details. I don't have anything else to offer to you.
One last thing I may propose is to make sure that "everyone" has "full control" on the directory where it is failing. Other than that make sure that the db is not locked but another user or process (I don't think this is the problem here but you never know).
January 8, 2007 at 6:47 am
I even changed the user/password of the MSSQLSERVER service to a domain administrator and am still getting the same problem as Philipp. Very strange indeed.
January 8, 2007 at 9:58 am
Even if the MSSQLSERVER service is a domain admin, it won't give network access to a SQL login.
A credential can be created that represents a Windows account, then if it is associated with a SQL login the credential account is used when the SQL user requests network resources.
January 8, 2007 at 11:03 am
So who is running the show? Who is accessing what?
If I'm logged in as a domain administrator and fire up Query Analyzer, login with a SQL Login (that happens to be a system administrator) and run the above OpenRowset thing why do I need additional credentials and to whom do I have to present them?
OK, so how do you create this credential thingy you have mentioned?
January 8, 2007 at 11:53 am
I'm assuming from the forum you posted the question in that you're using SQL 2005, in spite of your reference to Query Analyzer.
A SQL login cannot be a domain administrator. A SQL login is an entity maintained only within SQL Server, it has no connection with the Windows domain. Making the SQL Server service run as a domain admin MIGHT give the sa login domain admin rights (I don't know for sure), but it won't affect other SQL logins.
The restrictions on SQL logins not using the MSSQLSERVER account to access network resources is a security change in SQL 2005. Credentials were added so the network permissions of SQL logins could be managed separately.
Start with the "logins [SQL Server], credentials" topic in Books Online.
"A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login and password."
Amazingly enough, you create a credential with the CREATE CREDENTIAL command (or through the Management Studio object browser if you prefer a GUI). Then use ALTER LOGIN to map the credential to the login.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply