Solved

Cannot initialize the data source for linked server

Posted on 2009-04-06
41
1,120 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:dreinmann
  • 27
  • 11
  • 3
41 Comments
 
LVL 3

Expert Comment

by:sateeshcv
Comment Utility
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'
0
 

Author Comment

by:dreinmann
Comment Utility
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?


0
 
LVL 3

Expert Comment

by:sateeshcv
Comment Utility
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.

0
 

Author Comment

by:dreinmann
Comment Utility
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)
0
 
LVL 3

Expert Comment

by:sateeshcv
Comment Utility
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.

0
 

Author Comment

by:dreinmann
Comment Utility
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.
0
 

Author Comment

by:dreinmann
Comment Utility
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

0
 

Author Comment

by:dreinmann
Comment Utility
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

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0
 

Author Comment

by:dreinmann
Comment Utility
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)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.
0
 

Author Comment

by:dreinmann
Comment Utility
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!
0
 

Author Comment

by:dreinmann
Comment Utility
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

0
 

Author Comment

by:dreinmann
Comment Utility
I don't have to Rebuild/Reinstall my program after making the changes to the server, right?  I don't see that changing anything.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0
 

Author Comment

by:dreinmann
Comment Utility
Worked.

See screenshot.
SSMSqry.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, so now we can narrow it down a bit more...

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

Author Comment

by:dreinmann
Comment Utility
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?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Yep, would be first test....
0
 

Author Comment

by:dreinmann
Comment Utility
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

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
hmmmm.... let me think on that... must be missing soething obvious somewhere.
0
 

Author Comment

by:dreinmann
Comment Utility
Hello wall....meet Head!   ....bang....bang....bang!
0
 

Author Comment

by:dreinmann
Comment Utility
Should I try changing my connection string to 'master' for the database?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 ?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
We might end up calling a stored procedure rather than a select * from
0
 

Author Comment

by:dreinmann
Comment Utility
Any tables or queries that don't link to other databases outside of the server work just fine.
0
 

Author Comment

by:dreinmann
Comment Utility
And the ones that do link work fine when I run the app on the CPU with the server on it.
0
 

Author Comment

by:dreinmann
Comment Utility
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?
0
 

Author Comment

by:dreinmann
Comment Utility
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

Error.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

0
 

Author Comment

by:dreinmann
Comment Utility
Ok, created the linked server and tested it in the SSMS.
LinkSrvr.jpg
0
 

Author Comment

by:dreinmann
Comment Utility
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;"
0
 

Author Comment

by:dreinmann
Comment Utility
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

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Was that with connection 1 ?

Where was that wall.... Oh there it is... Head meet wall...

lets go through this logically...

1) server to server all OK as admin
2) running app on server as admin = ?
3) running app on server as z8b2330 = nogo
4) server to server  as z8b2330 = nogo

But, we are trying to connect as Admin.

But in Mixed Mode authentication and Windows Authentication it will always try to first accommodate the Windows user credentials, so, is most likely picking up  as z8b2330 reagardless. So, clearly it is user z8b2330 via server that does not have permission on the remote server (?)

0
 

Author Comment

by:dreinmann
Comment Utility
-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.
0
 

Author Comment

by:dreinmann
Comment Utility
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

0
 

Author Comment

by:dreinmann
Comment Utility
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
0
 

Author Comment

by:dreinmann
Comment Utility
(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!
0
 
LVL 51

Expert Comment

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

Author Comment

by:dreinmann
Comment Utility
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.
0
 

Author Comment

by:dreinmann
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now