linked server problem

I have two SQL server A and B and I want to access data from one to the other server.
I setup a linked server named 'LinkB' on A to link server B, I can see tables and views of B when I click the linked server node in EM. I can also execute select or SP of B in Query analyzer, It works well.

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A, and I can't execute SQL like 'select * from A.database.dbo.table' in Query analyzer.

I use 'sa' to connect the two SQL server.

who can tell me the real reason?
thankscan u give more imformation about servers?|||Originally posted by a1ex
can u give more imformation about servers?
the two server are all Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
I can connect to A and B in EM use same userid 'sa' and password.

Thanks very much!!|||who can help me with this problem? thanks!|||Originally posted by shine_law

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A

i noticed that you mentioned "linkA" and then said "link A" i thi sa typo or is this how you called it?|||I placed 'LinkA' on server B, 'LinkA' is the name of the lined server, which is used to access data on server A. Actully,I can call it any other name, suck as 'LinkFromBtoA'

thankssql

Linked Server Logon

Here is my setup, I'm setting up several Access .mdb's as linked servers.
The .mdb's are not password protected and they reside on our network. I can
get them in Sql server fine, and when I logon using my domain
username/password, I can get to them without any problem.
What I'm trying to do is setup a generic sql user who can access the linked
servers, but I keep on getting the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
I would like to use just a sql user to access the files like "sa", but I'll
set them up using my domain username/password if need be.
What am I missing?
Thanks,
BarryAs long as the "MS SQL Server" service's logon account has access to the UNC
where your files are located, you will make the connection using the linked
server security option "Be made without using a security context." Also,
just in case, check the default open mode and default record locking mode
for both the access db's.
They should be Shared, No Locks respectively.
Tools > Optons > Advanced Tab
Christopher Winn
Database Administrator
Fort Bend ISD
"Barry" <no_one@.home.net> wrote in message
news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
> Here is my setup, I'm setting up several Access .mdb's as linked servers.
> The .mdb's are not password protected and they reside on our network. I
can
> get them in Sql server fine, and when I logon using my domain
> username/password, I can get to them without any problem.
> What I'm trying to do is setup a generic sql user who can access the
linked
> servers, but I keep on getting the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication
> failed.
> [OLE/DB provider returned message: Cannot start your application. The
> workgroup information file is missing or opened exclusively by another
> user.]
> I would like to use just a sql user to access the files like "sa", but
I'll
> set them up using my domain username/password if need be.
> What am I missing?
> Thanks,
> Barry
>|||Thanks Chris, that did it.
Barry
"Winn, Chris" <seawinn@.hotmail.com> wrote in message
news:OPiqusnFEHA.2416@.TK2MSFTNGP12.phx.gbl...
> As long as the "MS SQL Server" service's logon account has access to the
UNC
> where your files are located, you will make the connection using the
linked
> server security option "Be made without using a security context." Also,
> just in case, check the default open mode and default record locking mode
> for both the access db's.
> They should be Shared, No Locks respectively.
> Tools > Optons > Advanced Tab
> Christopher Winn
> Database Administrator
> Fort Bend ISD
>
> "Barry" <no_one@.home.net> wrote in message
> news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
servers.
> can
> linked
> Authentication
> I'll
>

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server i
n
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/d...br />
5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...

>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle serve
r
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.sql

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server in
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...
>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle server
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.

Linked Server error 7399

I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

The drives are secured so that only local administrators have rights to them.

I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398

In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.

My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.

ThanksI figured it out. Here is what I found out:
Using the FileMon.exe tool from sysinternals.com I noticed that the file referred to in the error called "SYBINIT.ERR" could not be written since regular users did not have write access to the directory that it was trying to be written to. This was C:\WINNT\System32. So, I temporarily gave "Everyone" rights to modify the files within this directory. After doing so I ended up getting a "SYBINIT.ERR" file created which in turn told me that it "Cannot access file c:\sybase\ini\objectid.dat". I then gave Everyone read rights to the C:\sybase\ini folder. I then attempted again and got the following error: "Cannot access file c:\sybase\locales\locales.dat". Again, I gave read rights to this folder and tried again... this time getting the following errors: "Cannot access c:\sybase\charsets\iso_1\binary.srt file
Cannot access c:\sybase\charsets\iso_1\charset.loc file
Cannot access c:\sybase\charsets\iso_1\iso_1.cfg file"

Not wanting to keep this up, I then gave read rights to the C:\sybase folder and tried again. Everything worked. Then, I removed modify rights from "C:\WINNT\System32" and tried again. EVERYTHING WORKED!!! Yeah, this means that I just had to give read rights without opening up the SQL Server too much.|||Appreciate your interest in posting solution too, keep it up.

Linked server delegation problem, works for a while after reboots

I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
Martin
I think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin
|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin
|||Take a look at this
http://support.microsoft.com/default...b;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin

Linked server delegation problem, works for a while after reboots

I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
used the workaround from the article, which seems a little more
straightforward than mine and has the same effect.
Thanks,
Martin
"Terry" wrote:
> Take a look at this
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> It references 2005>2005, and your issue is 2005>2000 but I beleive only
> the error message changed. This after 3+ months working with Microsoft.
> Terry
> Martin M wrote:
> > I have two SQL servers setup with linked servers pointing back and forth
> > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > The second is running SQL Server 2000 SP4. Both are running on Windows
> > Server 2003 Enterprise SP1.
> >
> > Both linked servers are setup to make connections using the login's current
> > security context.
> >
> > Immediately after both servers have been rebooted, both linked servers work
> > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > starts returning the following error message for users connected using
> > Windows authentication:
> >
> > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have yet to see the linked server from SQL 2000 to 2005 fail.
> >
> > I saw two similar issues to this, neither of which has been resolved. Does
> > anybody know if this issue is specific to connections from 2005 to 2000?
> > Would upgrading the second server resolve it or make the issue affect both
> > linked servers?
> >
> > Thanks,
> > Martin
>|||I just discovered that the linked server I created using the example command
from Microsoft's workaround doesn't seem to work for SQL logins; the original
workaround I found, creating the linked server on an alias that uses named
pipes, works for both, so I'll stick with it.
Martin
"Martin M" wrote:
> Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
> used the workaround from the article, which seems a little more
> straightforward than mine and has the same effect.
> Thanks,
> Martin
>
> "Terry" wrote:
> > Take a look at this
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> >
> > It references 2005>2005, and your issue is 2005>2000 but I beleive only
> > the error message changed. This after 3+ months working with Microsoft.
> >
> > Terry
> >
> > Martin M wrote:
> > > I have two SQL servers setup with linked servers pointing back and forth
> > > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > > The second is running SQL Server 2000 SP4. Both are running on Windows
> > > Server 2003 Enterprise SP1.
> > >
> > > Both linked servers are setup to make connections using the login's current
> > > security context.
> > >
> > > Immediately after both servers have been rebooted, both linked servers work
> > > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > > starts returning the following error message for users connected using
> > > Windows authentication:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I have yet to see the linked server from SQL 2000 to 2005 fail.
> > >
> > > I saw two similar issues to this, neither of which has been resolved. Does
> > > anybody know if this issue is specific to connections from 2005 to 2000?
> > > Would upgrading the second server resolve it or make the issue affect both
> > > linked servers?
> > >
> > > Thanks,
> > > Martin
> >
> >

Linked server delegation problem, works for a while after reboots

I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix
.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/defaul...kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin

Linked Server connection problem

I have a SQL 2K Server that is behind a substacial
firewall, and I am trying to setup a Linked Server between
it and a SQL 2K server on a host site. I can set up the
link just fine frm my SQL 2K server at home, but am unable
to make the link from the SQL server at work. The firewall
is the suspect.
I have talked to our router guys and they report that when
I try to make the link, ports 445 and 139 are attempting
communication. These ports are specifically blocked by Net
Ops here.
My question is this, does the Linked Server proccess
require communications over these ports, or should I look
for another culprit '
ANY help is greatly appreciated !!!
Thanks.
Michael.SQL server uses port 1433 by default. Make sure it is
open on the firewall or spell out a different port to use
with your "Client Network Utility" and "Server Network
Utility"
>--Original Message--
>I have a SQL 2K Server that is behind a substacial
>firewall, and I am trying to setup a Linked Server
between
>it and a SQL 2K server on a host site. I can set up the
>link just fine frm my SQL 2K server at home, but am
unable
>to make the link from the SQL server at work. The
firewall
>is the suspect.
>I have talked to our router guys and they report that
when
>I try to make the link, ports 445 and 139 are attempting
>communication. These ports are specifically blocked by
Net
>Ops here.
>My question is this, does the Linked Server proccess
>require communications over these ports, or should I look
>for another culprit '
>ANY help is greatly appreciated !!!
>Thanks.
>Michael.
>.
>

Linked Server (SQL 2000 to SQL 6.5)

Hi,

I am trying to setup up a linked server using 'sp_addlinkedserver'. The host server is a SQL 2000 and the remote server is SQL 6.5. Both the servers are on the same domain.

When the system stored procedure is run, enterprise manager shows the name of the linked server but there are no tables.

I get error 'SQL Server does not exist or access denied' when trying to run query against a database on the remote server.

Does SQL server 6.5 support linked servers?

Please help,

DPKAOriginally posted by DPKA
Hi,

I am trying to setup up a linked server using 'sp_addlinkedserver'. The host server is a SQL 2000 and the remote server is SQL 6.5. Both the servers are on the same domain.

When the system stored procedure is run, enterprise manager shows the name of the linked server but there are no tables.

I get error 'SQL Server does not exist or access denied' when trying to run query against a database on the remote server.

Does SQL server 6.5 support linked servers?

Please help,

DPKA

Hi from me again,

now I am able to add the SQL server 6.5 database as a remote database but when trying to run a query against a table on this remote database, i get 'Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user '\'.

I have used the stored procedures sp_helplinkedsrvlogin and sp_addlinkedsrvlogin and have access to both the databases.

Any help appreciated,

DPKA|||Well...did you set up permissions on the linked server?

Even if you ass a linked login, it still has to be known to the server...

I have never tried adding a link to 6.5 though...

but I did find this in BOL

Note To use the Microsoft OLE DB Provider for SQL Server 2000 in SQL Server version 6.x, run the \Microsoft SQL Server\MSSQL\Install\Instcat.sql script against the version 6.x SQL Server. This script is essential for running distributed queries against an SQL Server 6.x server.

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:
>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> >
> > Is there anyway to set XACT_ABORT ON for a linked server? We have a
> linked
> > server setup between SQL 2000 and DB2. To use transactions we need to
> turn
> > XACT_ABORT on in every single stored procedure. Is there a way to turn it
> on
> > at the linked server level?
> >
> > Thanks.
> >
> > Paul
> >
> >
>
>

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it o
n
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF
."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paul
a.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>
|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked server - admin vs non-admin windows login

Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from this
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. This
> may help:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
> > Hi
> >
> > This look like a permissions issue with the non-administrator account. This
> > may help:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> >
> > John|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the access
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not have
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
> > Hi
> >
> > The may help further, it is not necessarilly the directory where the access
> > database is but the temp directory for the service account.
> >
> > http://support.microsoft.com/kb/814398
> >
> > I would also look at KB 285833 "PRB: Need to map to default Admin account
> > and use NULL for password to query linked server to Access database", but
> > this does not seem to be available. I think it says run a command similar to
> > the following: http://tinyurl.com/ox9h7
> >
> > John
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as the
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in which
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
> > Hi
> >
> > Assuming that you have added permissions to the database then there should
> > only be the directory in which it resides. Have you tried logging in as the
> > service account to access the access database?
> >
> > John
> >
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet on
> the administrators menu. You may have it configured as local system, in which
> case using a domain account may help. If it is already a domain account use
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
> > Hi
> >
> > The service account is the windows account that the service is set to run
> > under, you can see this in the configuration tool or the services applet on
> > the administrators menu. You may have it configured as local system, in which
> > case using a domain account may help. If it is already a domain account use
> > this account to log into the server and open the access database.
> > Requirements for a service account are detailed in Books Online or at
> > http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> >
> > John
>|||The server runs as a network service..
"John Bell" wrote:
> Hi
> Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> as there should be a default mapping see sp_addlinkedsrvlogin in Books
> Online. What does the acount information say on your service?
> John|||Hi
But in the services applet if you double click the SQL Server service what
does it say on the log on tab?
John
"Lolik" wrote:
> The server runs as a network service..
>
> "John Bell" wrote:
> > Hi
> >
> > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > Online. What does the acount information say on your service?
> >
> > John
>|||if i go to SQL Server Configuration Manager..
..SQL Server 2005 Services
.. SQL Server (MSSQLSERVER)
and double-click on it,
a) the log on tab says:
Log on as:
built-in account:
Network Service
b) Service tab says:
Name SQL Server (MSSQLSERVER)
SQL Service Type SQL Server
"John Bell" wrote:
> Hi
> But in the services applet if you double click the SQL Server service what
> does it say on the log on tab?
> John
> "Lolik" wrote:
> > The server runs as a network service..
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > Online. What does the acount information say on your service?
> > >
> > > John
> >
> >|||Hi
This may be related to your other problem of not being able to change the
service accounts.
John
"Lolik" wrote:
> if i go to SQL Server Configuration Manager..
> ..SQL Server 2005 Services
> .. SQL Server (MSSQLSERVER)
> and double-click on it,
> a) the log on tab says:
> Log on as:
> built-in account:
> Network Service
> b) Service tab says:
> Name SQL Server (MSSQLSERVER)
> SQL Service Type SQL Server
>
> "John Bell" wrote:
> > Hi
> >
> > But in the services applet if you double click the SQL Server service what
> > does it say on the log on tab?
> >
> > John
> >
> > "Lolik" wrote:
> >
> > > The server runs as a network service..
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > > Online. What does the acount information say on your service?
> > > >
> > > > John
> > >
> > >|||Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks|||Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
> > Any chance there was ever a resolution to this problem? We're having the
> > same issue and I cannot find the answer.
> >
> > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > folder. On the server, we had to give "Authenticated Users" read access to
> > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > we did this same thing and the initialization error went away but now our
> > queries just hang and never return. We have "Authenticated Users" in the
> > Users group on the server but if we add them to the Administrators group then
> > the queries run fine.
> >
> > Any ideas would be greatly appreciated.
> > Thanks
> >
> >|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
> > Hi Doug
> >
> > SQL 2005 has a tighter security model so making them a sysadmin would
> > probably bypass any more granular permissions you have. I would guess it is
> > more likely to be system objects that are causing the problems than user
> > ones. You could run profiler on the remote system to see what it does when
> > you have the permissions and where it stalls when you don't
> >
> > John
> >
> > "Doug" wrote:
> >
> > > Any chance there was ever a resolution to this problem? We're having the
> > > same issue and I cannot find the answer.
> > >
> > > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > > folder. On the server, we had to give "Authenticated Users" read access to
> > > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > > we did this same thing and the initialization error went away but now our
> > > queries just hang and never return. We have "Authenticated Users" in the
> > > Users group on the server but if we add them to the Administrators group then
> > > the queries run fine.
> > >
> > > Any ideas would be greatly appreciated.
> > > Thanks
> > >
> > >

Linked server - admin vs non-admin windows login

Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
John
"Lolik" wrote:

> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file usi
ng
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from thi
s
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:

> Hi
> This look like a permissions issue with the non-administrator account. Thi
s
> may help:
> http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
[vbcol=seagreen]
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
>|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:

> Hi
> The may help further, it is not necessarilly the directory where the acces
s
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar
to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:

> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not hav
e
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
>
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:

> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as th
e
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in whic
h
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:

> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
>
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:

> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet o
n
> the administrators menu. You may have it configured as local system, in wh
ich
> case using a domain account may help. If it is already a domain account us
e
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:

> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[
pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
>
>

Linked Server

I want to setup a linked server to MS Access.
If my MS Access file in different machine, on EM, once I got into the New
Linked Server page, General tab, what should I enter to the boxes:
Product name, Data source and Provider string ?
Have a look in BOL for sp_addlinkedserver, EG
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is @.server arbitrary ? Any string can work ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uZEcigd5FHA.552@.TK2MSFTNGP10.phx.gbl...
> Have a look in BOL for sp_addlinkedserver, EG
> EXEC sp_addlinkedserver
> @.server = 'SEATTLE Mktg',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Yes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Linked Server

I'm trying to setup a linked server from SQL server to another. Here's the
catch. One server is in a domain the other is in a workgroup. I can't get
the linking to work. Has anyone gotten this to work?What kind of security are you using for the linked server login?
You should not have any issues if you are using SQL authentication.
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Gates" <jgates@.someisp.com> wrote in message
news:%23mhRUUenFHA.2152@.TK2MSFTNGP14.phx.gbl...
> I'm trying to setup a linked server from SQL server to another. Here's
> the catch. One server is in a domain the other is in a workgroup. I
> can't get the linking to work. Has anyone gotten this to work?
>|||I'm using Windows Authentication on the server in the workgroup.
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:OtU5$ffnFHA.1468@.TK2MSFTNGP12.phx.gbl...
> What kind of security are you using for the linked server login?
> You should not have any issues if you are using SQL authentication.
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Gates" <jgates@.someisp.com> wrote in message
> news:%23mhRUUenFHA.2152@.TK2MSFTNGP14.phx.gbl...
>

Linked Server

I'm curious if any database guru has this experience.

I setup the ODBC connection with the 3rd party software CONNX. Linking the database from MS Access, I get the query result within 5 seconds. But when I use the linked server in SQL 2005 Express, nothing shows up after 5 minutes(I ended up cancelling the query). How would anyone resolve this issue? Thank you.

If I understand the issue correctly, you're using Linked Server in SQL Express to connect to a Microsoft Access database, right? Not sure what's happening, but try to new a test.udl file on your desktop, then double click to open it->choose the same provider as used in the Link Server->configure it to connect to the Access database file and test the connection. If the test succeeds, then use execute some as following to test the linked server:

select top 0 * fromyourAccessServerName...Table1