Start Free Trial

asked on

Cannot initialize the data source for linked server

I migrated a backend access database for my vb.net program to SQL Server 2005. Everything works when I run the program on the same machine that has the server on it. When I try to run the program on other machines in the network: it works when it has to access everything, except the Linked Servers. The 'Linked Servers' are Access DB's that are on a different server on the network. (and I get the error above)

Tried these suggestions:
Set sharing on the Temp directory to everyone/full control. (relocated the Temp directory to root of C:/ instead of Docs and Settings)

Went to Component Services, 'My Computer' the one that has a MS DTC tab on it. Set the access to Allow Outbound, Allow Inbound, (Mutual Authentication Required), Allow Remote Clients, and Allow Remote Administration (all checked)

Unchecked: Enable Transaction Internet Protocol (TIP) Transactions
Unchecked: Enable XA Transactions

MS Office is installed on the computer hosting the server. (my computer)

Still get the same error message.

??This is possible to do, right....set up a Server on a WinXP destop CPU that is networked on my company's server?? It's just the linked server's that aren't working, and those are elsewhere on the Company's server domain. What do I do...spent hours trying to figure this out...if I could offer 5000 points...I would!

HI,
EXECUTE FOLLOWING COMMANDS THROUGH VB.NET
BEFORE DOING THIS, PLEASE MENTION THE SERVERNAME IN THE FILED @DATASOURCE. SO REPLACE THE NAME ACCORDING TO THE SERVERNAME
-- GETTING ROWS FROM ANOTHER COMPUTER BY CREATING LINKED SERVER
--creating linked server for remoting accessing
EXEC SP_ADDLINKEDSERVER
      @SERVER='ANYNAME',
      @SRVPRODUCT='',
      @DATASRC='REMOTESERVERNAME',
      @PROVIDER='SQLNCLI';
      --OPTIONAL      @PROVSTR='Initial Catalog=master;'

--CREATING REMOTE SERVERLOGIN FOR SERVER 'ANYNAME' WITH USERNAME AND PASSWORD
EXEC SP_ADDLINKEDSRVLOGIN 'ANYNAME', 'false', null, 'sa', 'PASSWORD'

ASKER

sateeshcv,
Thanks for the quick reply...questions before I can try this:

Even though I've created the Linked Servers to the Access DB's, are you saying that they have to be sort-of recreated in the VB.NET program.
Here's the code I used to create the Linked Server in SQL Server 2005:
EXEC sp_addlinkedserver
@server = 'RNMDB',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet', -- Not 'Access'
@datasrc = 'G:\Operations\Transportation\01 Daily Database Transfer\Roadnet.mdb'
EXEC sp_addlinkedsrvlogin
@rmtsrvname= 'RNMDB',
@locallogin = NULL,
@useself= 'TRUE',
@rmtuser= 'Admin',
@rmtpassword = NULL
(Using this info., could you show me what to exec in Visual Studio?)

--EXECUTE FOLLOWING COMMANDS THROUGH VB.NET--
Thought maybe to do this, I'd go to the Server (Data Connection), Stored Procedures, right-click, Add New Stored Procedure, but it doesn't allow EXEC...only CREATE or ALTER. So, I must be doing that wrong (sorry, very new to using SQL, especially using it in VS) ... please elaborate. Do I just execute it in a Query?

HI,
SORRY, I THOUGHT UR MIGRATING DATABASE USING VB.NET, SO I HAD MENTIONED TO USE VB.NET
ANYHOW UR USING SQL SERVER 2005, U CAN DIRECTLY EXECUTE THE COMMANDS DIRECTILY WHAT YOU HAVE SPECIFIED.

NOTE: U CAN JUST QUERY THE VALUES FROM LINKED SERVER NOT ANY DDL STATEMENTS LIKE CREATE, DROP OR ALTER UNLESS YOU HAVE PERMISSIONS.

ASKER

Right, let me show you what I've got.
(Created the Linked Server using the code above)
From that, there is a view that queries the Linked Server as such:

SELECT *
FROM RNMDB...RS_ROUTE AS RS_ROUTE_1

There are other views after this that my program accesses and it works fine on the CPU that has the server installed, but on other computers, it doesn't. (again, RNMDB is an Access DB on our company's network server)

Hi ,
So when u create linked server, u can access the queries in ur system. No problem in that.
But ur accessing from other computers with same command means wont work, because those servers has not created the linked server for the DB RNMDB in the current machine..
So create Linked servers for all the computers who wants the RNMDB values.

ASKER

I thought that creating a Linked Server to the Access DB in the server would allow me to create a view that could be accessed by anyone that can access the server.

ASKER

Tried changing over to a Domain Account Service, think I did it right....still everything working except the Linked Servers on other CPU's. See error below.

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "RNMDB".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable)

Open in new window

ASKER

Tried something else.
Changed back to 'Local System' account.
Changed Linked Server to OPENROWSET
Almost the same error...a little different though...see below. (Still works when I run the program on the machine where the Server's installed.

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable)

Open in new window

Nah, local system account has no presence on any of the other servers. The SQL Servers have to be part of a trusted network across those two / three server instances. So, you were better off using a "real" user as the owner / person running up the services. If you re able to run a VB app on your box that can read the other server data, then try running up the SQL services (sql server, sql server agent, sql browser) as your username, just for the purposes of a quick test (and only if in a position to do so). Have to stop SQL server, then the services, then start them up again as the new user etc...

ASKER

Do I understand this correctly?
-I should be using a 'Built-in account' > Network Service on all services.
-I should start them with my Company Network Id & Password. (same network id that can access the mdb that I want to link to with OPENROWSET)

1) No not one of the built in accounts but an account that can be "known" to the other servers like a domain user.
2) Yes.

ASKER

I tried Domain at one point in time (see 4/7/09 10:28a post). I will try again...must have did something wrong, maybe you can help me through it.
I'm going to change all the Services over to Domain account, using the same account Domain\userid that I use on my company's network.

Then try it? Or, is there something else that needs to be done in 'SQL Server Management Studio'?

How should the TCP/IP and Named Pipes be set up?
(Current):
Named Pipes - Enabled=Yes | Pipe Name=\\.\pipe\sql\query
TCP/IP - Enabled=Yes | Keep Alive = 120000 | Listen All=Yes | No Delay=No
VIA - Enable=No
(Order) Shared Mem, TCP/IP(Port=1433), Named Pipes(DfltPipe=sql\query)

Surface Area Config for Remote Connections is set to -Local and remote connections=Using both TCP/IP and named pipes.

...Man I hope this works!

ASKER

Same error....still doesn't work on other network CPU's.

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable)

Open in new window

ASKER

I don't have to Rebuild/Reinstall my program after making the changes to the server, right? I don't see that changing anything.

OK, first step is let's try it as a query in SSMS inside a query window. Let's try to isolate if it is SQL Server to SQL Server having the problem...

ASKER

Worked.

See screenshot.
SSMSqry.jpg

OK, so now we can narrow it down a bit more...

How is your app logging into the server ? And as what user ?

ASKER

Think this might have something to do with it:

Public TWConnString As String = "Server=3K-HN7ZGF1;User Id=Admin; Password=***********; Database=LogisticsManager;Trusted_Connection=True;"

UserID & Pass should probably that same NetworkID and Password, right?

Yep, would be first test....

ASKER

Nope....changed User ID and Pass to the Domain\UserID & Pass...got the same error.

Here's the total code on the connection to the server/database. The "EmployeeTimeOffFnl" is a View that references another View that is an OPENROWSET of a mdb elsewhere on the network.

Public TWConnString As String = "Server=3K-HN7ZGF1;User Id=Admin; Password=***********; Database=LogisticsManager;Trusted_Connection=True;"
 
 
Dim da26 As New SqlClient.SqlDataAdapter("SELECT * FROM EmployeeTimeOffFnl", cn)
            Dim ds26 As New DataSet()
            ds26.EnforceConstraints = False
            da26.FillSchema(ds26, SchemaType.Source, "EmployeeTimeOffFnl")
            da26.Fill(ds26, "EmployeeTimeOffFnl")
            Dim cmdBuilder26 As New SqlClient.SqlCommandBuilder(da26)

Open in new window

hmmmm.... let me think on that... must be missing soething obvious somewhere.

ASKER

Hello wall....meet Head! ....bang....bang....bang!

ASKER

Should I try changing my connection string to 'master' for the database?

That could be it.... what is the default database for that user ?

If you swap your query to be another very ordinary table, does it work ?

We might end up calling a stored procedure rather than a select * from

ASKER

Any tables or queries that don't link to other databases outside of the server work just fine.

ASKER

And the ones that do link work fine when I run the app on the CPU with the server on it.

ASKER

Tried many different combinations of connection strings. Tried changing the database to =master. Changed the default database for that uid to LogisticsManager.

Think a stored procedure might work? What do I do?

ASKER

I was able to recreate the same error on the server machince that occurs on the other machines.

In the OPENROWSET code, I changed the mdb userid from 'admin' to 'z8b2330' and it generated the error shown in the pic below...same one I get when I try to run the app on the other machines.

So, the problem may be that the userid - 'admin' is not carrying over when it's executed from the other machines? Why?

SELECT     *
FROM         OPENROWSET('Microsoft.Jet.OLEDB.4.0', '\\3kxv03sfs01\Group\Operations\Transportation\01 Daily Database Transfer\Roadnet.mdb'; 'z8b2330'; '', 
                      TS_LOCATION) AS TS_LOCATION_1

Open in new window

that is because it is like an ad-hoc linked server and uses the logged in credentials...

when you normally run that query, it does have the user od admin in place of z8b2330 ?

can we create a formal linked server ?

-- step 1 use code (or SSMS) to create a linked server - a once off process, but need to check and might need to restart after server boot / reload etc.
 
EXEC sp_addlinkedserver 
	@server = 'Access_RoadNet', 
	@provider = 'Microsoft.Jet.OLEDB.4.0', 
	@srvproduct = 'OLE DB Provider for Jet',
	@datasrc = '\\3kxv03sfs01\Group\Operations\Transportation\01 Daily Database Transfer\Roadnet.mdb''
GO
 
-- 
EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = 'Access_RoadNet',
	@useself = 'FALSE',
	@locallogin = NULL, 
	@rmtuser = 'Admin', 
	@rmtpassword = NULL
GO
 
-- now we can run queries using the linked server 
-- prefer to use the four part identifier
 
SELECT * FROM Access_RoadNet...TS_LOCATION
 
-- or use openquery
 
SELECT * FROM OPENQUERY(Access_RoadNet, 'SELECT * FROM TS_LOCATION') 
GO
 
-- then when no longer needed drop the server - or leave it there for "next time"
 
sp_dropserver Access_RoadNet, droplogins

Open in new window

ASKER

Ok, created the linked server and tested it in the SSMS.
LinkSrvr.jpg

ASKER

Which way would you suggest for the connection string?

#1 - Public AccessConnString As String = "Server=<?ServerName or IP?>;User Id=admin; Password=; Database=LogisticsManager;Trusted_Connection=True;"

#2 - Public AccessConnString As String = "Server=<?ServerName or IP?>;User Id=<Domain\UserID>; Password=********; Database=LogisticsManager;Trusted_Connection=False;"

#3 - Public AccessConnString As String = "Server=tcp:<?ServerName or IP?>,Port;Database=LogisticsManager;Integrated Security=SSPI;"

#4 - Public AccessConnString As String = "Server=<?ServerName or IP?>;User Id=<Domain\UserID>; Password=********; Database=LogisticsManager;Trusted_Connection=True;"

ASKER

Still get an error.

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Access_RoadNet".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable)
   at WindowsApplication1.frmDrvrSchdr.frmDrvrSchdr_Load(Object sender, EventArgs e) in C:\Documents and Settings\z8b2330\My Documents\Current Projects\PermRtingUpdate\UTPrmRtgAutoMac\UTPrmRtgAutoMac\frmDrvrSchdr.vb:line 292

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

-Yes that was connection #1 w/ ServerName (not IP).

-I know, I have never worked this hard on a single problem ever...usually after an hour or two of my own research, I go to EE, one of you guys figure it out in minutes, and I'm on my way.

-1)?means? >> I can run a query successfully from the server on the Access.mdb residing on the company's network.
-2) I can run the app on the cpu with the server installed w/ any one of the previously mentioned connstrings
-3) Where I used this UserId, I don't think I had my <domain\> in front of it, so that's probably why that didn't work.
-4) Running the app on any other networked machine will work with All (server tables and views) 'EXCEPT' the one's that use the Linked Server mdb's residing on the network.

ASKER

I ran a Filemon. I get this when I execute the program on the other CPU.

3:38:09 PM	sqlservr.exe:540	QUERY INFORMATION	C:\WINDOWS\system32\system.mdb	NOT FOUND	Attributes: Error	
3:38:09 PM	sqlservr.exe:540	DIRECTORY	C:\WINDOWS\system32\	NO SUCH FILE	FileBothDirectoryInformation: system.mdb	
3:38:09 PM	sqlservr.exe:540	OPEN	C:\WINDOWS\system32\system.mdb	NOT FOUND	Options: Open  Access: 0012019F	
3:38:09 PM	sqlservr.exe:540	OPEN	C:\WINDOWS\system32\system.mdb	NOT FOUND	Options: Open  Access: 00100080

Open in new window

ASKER

Did a more extensive Filemon for the moment in time when the Button is clicked on the app where it needs to access the Server and linked servers.
There's one for when I run the app on the same machine as the server.
There's one for when I run the app on a remote machine.
FilemonSrvrMach.LOG
FilemonRemoteMach.LOG

ASKER

(screaming loudly) I FINALLY FIGURED IT OUT!!! WOOOOOOHOOOOOO!!!!!

Here's what WON'T work:
Any combination of the following configurations - Sqlclient connection strings(tried 11 different ones), Local/Network/Server/Domain authorizations, Linked Servers by Linked Server, Openrowset, OpenDataSource, Views or Stored Procedures, Server Surface Area configurations, different Service s configurations, and there's probably more I worked on!

Here's what did work:
Switching to an SQL Native Client ODBC connection setup, but this wasn't enough...at first, I still got pretty much the same error. Had to change the temp documents location to 'C:\Temp', then give permissions to the users from the remote computers.
Used a Network Authorization Service setup btw. It may work with others, but I didn't try.

Thanks for all of your help Mark...though you may not have provided the solution, you gave me many things to try and I learned a lot along the way!

Well done, and love your reaction ! Very happy to have provided some help along the way. Love that woooohoooo :) :)

ASKER

Gonna post a question (should be a quick one) for you if you have time. Now that I have this working for the DB Owner - Domain\UserID on remote CPUs, I need to try to get it to work with other UserIDs.

ASKER

https://www.experts-exchange.com/questions/24329583/SQL-Server-Login-Error.html