Solved

Cannot initialize the data source for linked server

Posted on 2009-04-06
41
1,161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 11
  • 3
41 Comments
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24077349
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
ID: 24078284
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
ID: 24085579
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dreinmann
ID: 24087139
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
ID: 24087617
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
ID: 24087660
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
ID: 24088369
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
ID: 24092335
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
ID: 24099146
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
ID: 24100233
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
ID: 24100361
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
ID: 24100616
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
ID: 24100774
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
ID: 24100807
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
ID: 24101769
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
ID: 24102047
Worked.

See screenshot.
SSMSqry.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24102074
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
ID: 24102170
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
ID: 24102343
Yep, would be first test....
0
 

Author Comment

by:dreinmann
ID: 24102345
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
 
LVL 51

Expert Comment

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

Author Comment

by:dreinmann
ID: 24102530
Hello wall....meet Head!   ....bang....bang....bang!
0
 

Author Comment

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

Expert Comment

by:Mark Wills
ID: 24103446
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
ID: 24103448
We might end up calling a stored procedure rather than a select * from
0
 

Author Comment

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

Author Comment

by:dreinmann
ID: 24107001
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
ID: 24107480
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
ID: 24108912
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
ID: 24109406
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
ID: 24110256
Ok, created the linked server and tested it in the SSMS.
LinkSrvr.jpg
0
 

Author Comment

by:dreinmann
ID: 24110472
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
ID: 24110503
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
ID: 24110765
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
ID: 24110908
-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
ID: 24111341
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
ID: 24112012
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
ID: 24160522
(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
ID: 24161123
Well done, and love your reaction ! Very happy to have provided some help along the way. Love that woooohoooo :) :)
0
 

Author Comment

by:dreinmann
ID: 24162031
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
ID: 24162102
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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