Linked Sql Servers

Trying to link 2 servers via:
SELECT *
from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=p wd;
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=p wd;
> 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...
>

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?
>

server, DTC, SQLOLEDB, Access, linked tables = HEADACHE!

OK, welcome to my nightmare... I got 2 SQL Servers and an Access DB Front end

SQL Server1: SQL Server 7.0 (on our network)
SQL Server2: SQL Server 2000 (remote)
AccessDB: on our network

There is a Form in the Access DB that is used by Sales & Marketing to update data in both SQL Servers and there are subqueries involved. I tried just linking the tables in Access, but the connection to the remote SQL Server keeps getting dropped within seconds of opening it regardless of whatever timeouts I set anywhere. The linked tables to the local SQL Server stay connected just fine (both DSNs on the Access machine are using TCP/IP).

So, I tried adding SQL Server2 as a Linked Server to SQL Server1, then write a distributed view object (SELECT * FROM [linkedserver].[catalog].[dbo].[table]) the table I need. Then I create a linked table in Access to the new view. Well I can SEE the data fine (SELECT), but in order to update the data, it evidently requires DTC, but since it's SQLOLEDB (all together now) "does not support distributed transactions." Yup DTC is running just fone on both SQL Servers, nope we can't upgrade the SQL Server 7.0

If anyone has ANY insight into this mess, I'm all ears.Can you call an SP to do the update?
set transact abort on gets round some transaction incorporation problems but I doubt if it would solve this.|||no, I can't do an SP in this situation. It's just MS Access updating a linked table (view). I did try to find a way to do this with an SP to employ the trasact abort thing, but it didn't apply.|||I think I'm going to X-Post this in the usenet forums. Seems to be a lot of traffic in there. I'll be a nice x-poster though, if I get an answer in one forum, i'll post it in the other.