Linked Sql Servers

Trying to link 2 servers via:
SELECT *
from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
Trusted_Connection=yes;',
'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
-or-
select * from krusty.Web_Configurator.dbo.MC_WS_Countries
Keep getting the dreaded:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I ran the following scripts and verified that the database server I'm
attempting to reach is in mixed mode:
exec sp_addlinkedserver @.server='krusty'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser = 'uid',@.rmtpassword = 'pwd'
How I can check each server for correct configuration and get this simple
query working?Make sure that the remote server is using SQL Server Authentication,
the error message indicates that it is expecting Windows
authentication only.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Trusted_Connection =yes means use Windows Authentication even if you suply a
UID and a Password. The system is doing exactly what you told it to do.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"dbach" <dbach@.discussions.microsoft.com> wrote in message
news:C5EAB394-091D-4886-A518-55099CC2342B@.microsoft.com...
> Trying to link 2 servers via:
> SELECT *
> from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
> Trusted_Connection=yes;',
> 'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
> -or-
> select * from krusty.Web_Configurator.dbo.MC_WS_Countries
> Keep getting the dreaded:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I ran the following scripts and verified that the database server I'm
> attempting to reach is in mixed mode:
> exec sp_addlinkedserver @.server='krusty'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser => 'uid',@.rmtpassword = 'pwd'
> How I can check each server for correct configuration and get this simple
> query working?|||Thanks Geoff. After removing Trusted_Connection=yes... I now see a new error:
Note... SSA_ERPDB.results exists. tried SSA_ERPDB.dbo.results as well
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ssa_erpdb.results'.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
"Geoff N. Hiten" wrote:
> Trusted_Connection =yes means use Windows Authentication even if you suply a
> UID and a Password. The system is doing exactly what you told it to do.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "dbach" <dbach@.discussions.microsoft.com> wrote in message
> news:C5EAB394-091D-4886-A518-55099CC2342B@.microsoft.com...
> > Trying to link 2 servers via:
> >
> > SELECT *
> > from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
> > Trusted_Connection=yes;',
> > 'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
> >
> > -or-
> >
> > select * from krusty.Web_Configurator.dbo.MC_WS_Countries
> >
> > Keep getting the dreaded:
> >
> > Msg 18452, Level 14, State 1, Line 1
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I ran the following scripts and verified that the database server I'm
> > attempting to reach is in mixed mode:
> >
> > exec sp_addlinkedserver @.server='krusty'
> > exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser => > 'uid',@.rmtpassword = 'pwd'
> >
> > How I can check each server for correct configuration and get this simple
> > query working?
>