Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/defaul...b;en-us;274098. I then adde
d
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
JamesI am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/defaul...b;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/default...;en-us;274098. I then added
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
James
I am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/default...;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098. I then added
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
JamesI am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

Linked Server not wanting to connect

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB19', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB19',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'use remote collation', @.optvalue=N'true'

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB21', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB21',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'use remote collation', @.optvalue=N'true'

=========== CODE END ==========

Now I have masked the real values in this post of the @.provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.

@.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

Is the remote server AUS02DB21 a 2005 server? Does it enable remote access?

Can you access the server from your utility server by SSMS?|||

Yes ... the instance I am creating the linked server to is a SQL Server 2005 SP2 instance.

It has remote access enabled.

I am also able, and have for quite some time, a registered instance in SSMS on the server I am creating the linked server on.

What is interesting is that DB19 and DB21 are identical servers in our farm. Same hardware and software.

I am now getting this error message when I try to view the Catalogs of the newly created linked server:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

TCP Provider: The specified network name is no longer available.
(Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

BUTTONS:

OK

Any clue with this added error message?

Thanks for all the help!!

|||

I have found something rather interesting.

In the Windows Application event log I am seeing error about a connection coming from the server I have the linked server created on trying to access the DAC.

Here is the event:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 10.100.10.7]

So why in the heck is the DAC being accessed?|||

I got the same problem on SQL server 2005 SP2.

It creates the link but when I try to test it: "The test connection to the linked server failed."An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "server_name" returned message "Invalid server name was specified.". (.Net SqlClient Data Provider)

I'm trying to establish a linked server between SQL server 2005 SP2 and a Sybase server (ASE)

Thanks

|||

Hey, I got it working:

I had to put a provider string:

Server Name=server_name,port_no

(server_name would be what ever your servername would be

port_no: 5000 for sybase ASE)

now I need to know of a way to replicate the server w/out having the replication agent on the server (since the admin of the ASe server doesn'T want to unable this option agrrr!!!)

linked server dynamic SQL help

declare @.var varchar(50)
select @.var='ServerA'
select name from @.var.master.dbo.sysobjects -- Need help here
Right now its just one server but eventually i want to probably use a group
of servers and kinda use a cursor.You can do it via dynamic sql
declare @.var varchar(50)
select @.var='ServerA'
exec ('select name from '+ @.var + '.master.dbo.sysobjects')
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> declare @.var varchar(50)
> select @.var='ServerA'
> select name from @.var.master.dbo.sysobjects -- Need help here
> Right now its just one server but eventually i want to probably use a
group
> of servers and kinda use a cursor.
>|||Yup i should have mentioned that i tried that.. and thats why posted the
other message with some code. Its where I want to get it in a cursor
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
> You can do it via dynamic sql
> declare @.var varchar(50)
> select @.var='ServerA'
> exec ('select name from '+ @.var + '.master.dbo.sysobjects')
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> group
>|||Why do you want to use a cursor? They can be performance hogs.
For static sets of data, I use set operations.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OtHIKr7DFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Yup i should have mentioned that i tried that.. and thats why posted the
> other message with some code. Its where I want to get it in a cursor
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
>

Linked Server

I posted this in another forum and got no response, so I'll try here:
I’ve got two servers, say ServerA and ServerB, that are Windows 2000 Adv'd
Server SP4 & Sql Server 2000 SP4 . We are going to have to go to a tighter
security enviroment where each user has their own login for auditing
purposes. What would be ideal from manageability is for each user to have
their Windows login mapped into a Domain level group, call it domain\groupa.
This works except for one thing: I cannot get the linked servers to work.
Actually, I can get a painful version of it to work: I can map a windows
login to both servers, and then run the following on ServerA:
sp_addlinkedsrvlogin 'serverb', 'false', 'domaina\winlogina', 'sa',
'sapasswordforserverb'
This works but defeats the whole purpose of using the groups! So then I try
the following so I can use the domain level group acct:
EXEC sp_addlinkedsrvlogin 'serverb', 'false', ‘domaina\groupa, 'sa', '
sapasswordforserverb '
The command succeeds and shows up in the linked server properties security
tab. But when I run a query
select * from serverb.admindatabase.dbo.smalltable
from servera through the linked server it fails with the following:
“Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”
Why does this fail? It's important to note that the domaina\groupa group
account is mapped into both servers as system administrator and it is a
domain level account! Why can I get this to work with a regular windows
account that is NOT domain level but not with an administrative domain
account'
If I can’t this to work, it’s going to create a big administrative heada
che
for me…And, btw, my goal is not to use a domain level administrative accou
nt
with regular logins - I'm just trying to get this to work.Try here:
http://support.microsoft.com/kb/925001
"CLM" wrote:

> I posted this in another forum and got no response, so I'll try here:
> I’ve got two servers, say ServerA and ServerB, that are Windows 2000 Adv
'd
> Server SP4 & Sql Server 2000 SP4 . We are going to have to go to a tighte
r
> security enviroment where each user has their own login for auditing
> purposes. What would be ideal from manageability is for each user to have
> their Windows login mapped into a Domain level group, call it domain\group
a.
> This works except for one thing: I cannot get the linked servers to work.
> Actually, I can get a painful version of it to work: I can map a windows
> login to both servers, and then run the following on ServerA:
> sp_addlinkedsrvlogin 'serverb', 'false', 'domaina\winlogina', 'sa',
> 'sapasswordforserverb'
> This works but defeats the whole purpose of using the groups! So then I t
ry
> the following so I can use the domain level group acct:
> EXEC sp_addlinkedsrvlogin 'serverb', 'false', ‘domaina\groupa, 'sa', '
> sapasswordforserverb '
> The command succeeds and shows up in the linked server properties security
> tab. But when I run a query
> select * from serverb.admindatabase.dbo.smalltable
> from servera through the linked server it fails with the following:
> “Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”
> Why does this fail? It's important to note that the domaina\groupa group
> account is mapped into both servers as system administrator and it is a
> domain level account! Why can I get this to work with a regular windows
> account that is NOT domain level but not with an administrative domain
> account'
> If I can’t this to work, it’s going to create a big administrative hea
dache
> for me…And, btw, my goal is not to use a domain level administrative acc
ount
> with regular logins - I'm just trying to get this to work.
>