servers

sql 2000
I am completly lost.
While on dev01 I run:
select count(*)
from [DEV03].DBADB_local.dbo.Servers
I get:
Server: Msg 7411, Level 16, State 1, Line 1
Server 'DEV03' is not configured for DATA ACCESS.
DEV03 is not listed in EM under Security\Linked Servers on dev01 where I ran
this.
When I tried to add a linked server on dev01 to dev03 in EM, or using:
USE master
GO
EXEC sp_addlinkedserver
'DEV03',
N'SQL Server'
GO
I got the message:
Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'DEV03' already exists.
When I run 'sp_linkedservers' I get:
DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
When I run 'sp_helpserver' I get:
DEV01 DEV01 rpc,sub,rpc out,use remote collation 0
NULL 0 0
DEV03 DEV03 rpc,sub,rpc out,use remote collation 3
NULL 0 0
JayOn dev01 I ran:
sp_serveroption 'dev03', 'data access', 'TRUE'
and the message on dev03 changed to:
Server: Msg 18452, Level 14, State 1, Line 8
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
"Jay" <nospan@.nospam.org> wrote in message
news:u185b%23u$HHA.2004@.TK2MSFTNGP06.phx.gbl...
> sql 2000
> I am completly lost.
> While on dev01 I run:
> select count(*)
> from [DEV03].DBADB_local.dbo.Servers
> I get:
> Server: Msg 7411, Level 16, State 1, Line 1
> Server 'DEV03' is not configured for DATA ACCESS.
> DEV03 is not listed in EM under Security\Linked Servers on dev01 where I
> ran this.
> When I tried to add a linked server on dev01 to dev03 in EM, or using:
> USE master
> GO
> EXEC sp_addlinkedserver
> 'DEV03',
> N'SQL Server'
> GO
> I got the message:
> Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line
> 79
> The server 'DEV03' already exists.
> When I run 'sp_linkedservers' I get:
> DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
> DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
> When I run 'sp_helpserver' I get:
> DEV01 DEV01 rpc,sub,rpc out,use remote collation 0
> NULL 0 0
> DEV03 DEV03 rpc,sub,rpc out,use remote collation 3
> NULL 0 0
>
> Jay
>|||Jay,
Did you map logins? Sp_addlinkedsrvlogin (or something like that). Also, the remote server might be
in Windows only mode, so mapping to an SQL login won't jive in that case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:ejdKgQv$HHA.1188@.TK2MSFTNGP04.phx.gbl...
> On dev01 I ran:
> sp_serveroption 'dev03', 'data access', 'TRUE'
> and the message on dev03 changed to:
> Server: Msg 18452, Level 14, State 1, Line 8
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>
> "Jay" <nospan@.nospam.org> wrote in message news:u185b%23u$HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> sql 2000
>> I am completly lost.
>> While on dev01 I run:
>> select count(*)
>> from [DEV03].DBADB_local.dbo.Servers
>> I get:
>> Server: Msg 7411, Level 16, State 1, Line 1
>> Server 'DEV03' is not configured for DATA ACCESS.
>> DEV03 is not listed in EM under Security\Linked Servers on dev01 where I ran this.
>> When I tried to add a linked server on dev01 to dev03 in EM, or using:
>> USE master
>> GO
>> EXEC sp_addlinkedserver
>> 'DEV03',
>> N'SQL Server'
>> GO
>> I got the message:
>> Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
>> The server 'DEV03' already exists.
>> When I run 'sp_linkedservers' I get:
>> DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
>> DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
>> When I run 'sp_helpserver' I get:
>> DEV01 DEV01 rpc,sub,rpc out,use remote collation 0 NULL 0 0
>> DEV03 DEV03 rpc,sub,rpc out,use remote collation 3 NULL 0 0
>>
>> Jay
>|||SQL Server 2000
Well, Tibor was right about the login, however, that doesn't solve my issue.
To start, I'm deleting all linked servers on the dev and QA machines and
will recreate then as I need one at a time. However, there is one that
refuses to go.
QA01 has no linked servers, or replication defined in EM.
sp_helpdistpublisher & sp_helpsubscription both return no rows on QA01 (did
I get the commands right?)
DEV01
In EM I see QA01 listed in Linked Servers, when I tried to delete it I get
the message:
Cannot drop 'QA01' because it is used as a Subscriber in
replication.
sp_helpdistpublisher returns:
DEV01 distribution 1 sa 1 '\\DEV01\e$\Program Files\Microsoft SQL
Server\MSSQL\ReplData' 0 0
sp_helpsubscription returns nothing
On both dev01 & qa01 the output of sp_helpreplicationdboption is
db01 1 0 0 1 0
db02 2 0 0 1 0
db03 3 0 0 1 0
db04 4 0 0 1 0
db05 5 0 0 1 0
db06 6 0 0 1 0
db07 7 0 0 1 0
db08 8 0 0 1 0
db09 9 0 0 1 0
db10 10 0 0 1 0
db11 11 0 0 1 0
db12 12 0 0 1 0
No one knows anything about setting up replication between dev01 & qa01
either.
Jay|||OK, I cleared the link servers from dev01 & dev03 and ran the following
code. But I still can't do a simple select.
What I want to do is link the two servers such that only the DBADB_local
database can be accessed over the link. The final destination for these
links will be between servers that wouldn't otherwise be linked and will
include all production, QA and development servers.
-- Set a local database
-- On DEV03
USE DBADB_local
/*
-- Created a basic local Windows account on DEV03 for DBADB_local
-- Delete user (probably best if you comment this out, but I'm using it for
testing.
EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername ='DBADB_local'
EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername ='DBADB_local'
EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
EXEC sp_droplogin @.loginame = 'DBADB_local'
*/
-- Create the user account
EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb ='DBADB_local'
EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername ='DBADB_local'
EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername ='DBADB_local'
-- When accessing the 'Database Access' tab for the DBADB_local DB, I get an
error about 'Users Collection'
-- which I do not understand.
-- Reminder, still on DEV03
-- Probably comment
--EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
@.provider='SQLOLEDB', @.datasrc='DEV01'
--EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
tried both
EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
exec sp_helpserver
/* returns
DEV01 NULL data access,use remote collation 1 NULL 0 0
*/
-- Move to DEV01
-- Create basic local account in Windows.
-- Use same code to create the DBADB_local user on DEV01
EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
--EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
SELECT * FROM DEV01.DBADB_local.dbo.Servers
/* returns
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
*/|||Also:
sp_helplogins 'DBADB_local'
/* returns
DBADB_local 0x40AFA68175127440906B28234CCD413D DBADB_local us_english yes
no
DBADB_local DBADB_local db_datareader MemberOf
DBADB_local DBADB_local db_datawriter MemberOf
DBADB_local DBADB_local DBADB_local User
*/
on both machines.
"Jay" <nospan@.nospam.org> wrote in message
news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
> OK, I cleared the link servers from dev01 & dev03 and ran the following
> code. But I still can't do a simple select.
> What I want to do is link the two servers such that only the DBADB_local
> database can be accessed over the link. The final destination for these
> links will be between servers that wouldn't otherwise be linked and will
> include all production, QA and development servers.
>
> -- Set a local database
> -- On DEV03
> USE DBADB_local
> /*
> -- Created a basic local Windows account on DEV03 for DBADB_local
> -- Delete user (probably best if you comment this out, but I'm using it
> for testing.
> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
> EXEC sp_droplogin @.loginame = 'DBADB_local'
> */
> -- Create the user account
> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb => 'DBADB_local'
> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get
> an error about 'Users Collection'
> -- which I do not understand.
> -- Reminder, still on DEV03
> -- Probably comment
> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
> @.provider='SQLOLEDB', @.datasrc='DEV01'
> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
> tried both
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin => 'DBADB_local'
> exec sp_helpserver
> /* returns
> DEV01 NULL data access,use remote collation 1 NULL 0 0
> */
> -- Move to DEV01
> -- Create basic local account in Windows.
> -- Use same code to create the DBADB_local user on DEV01
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>
> SELECT * FROM DEV01.DBADB_local.dbo.Servers
> /* returns
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> */
>|||Huge hurdle cleared. I replaced:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
with
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false',
@.locallogin = NULL, @.rmtuser = 'DBADB_local', @.rmtpassword = 'foobar'
and it works.
What else is gonna bite me?
"Jay" <nospan@.nospam.org> wrote in message
news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
> OK, I cleared the link servers from dev01 & dev03 and ran the following
> code. But I still can't do a simple select.
> What I want to do is link the two servers such that only the DBADB_local
> database can be accessed over the link. The final destination for these
> links will be between servers that wouldn't otherwise be linked and will
> include all production, QA and development servers.
>
> -- Set a local database
> -- On DEV03
> USE DBADB_local
> /*
> -- Created a basic local Windows account on DEV03 for DBADB_local
> -- Delete user (probably best if you comment this out, but I'm using it
> for testing.
> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
> EXEC sp_droplogin @.loginame = 'DBADB_local'
> */
> -- Create the user account
> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb => 'DBADB_local'
> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get
> an error about 'Users Collection'
> -- which I do not understand.
> -- Reminder, still on DEV03
> -- Probably comment
> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
> @.provider='SQLOLEDB', @.datasrc='DEV01'
> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
> tried both
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin => 'DBADB_local'
> exec sp_helpserver
> /* returns
> DEV01 NULL data access,use remote collation 1 NULL 0 0
> */
> -- Move to DEV01
> -- Create basic local account in Windows.
> -- Use same code to create the DBADB_local user on DEV01
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>
> SELECT * FROM DEV01.DBADB_local.dbo.Servers
> /* returns
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> */
>|||> What else is gonna bite me?
A lot. <g>
Check out the parameter list for sp_addlinkedsrvlogin. You specified NULL for @.locallogin. Now
*every* local login are mapped to the 'DBADB_local' on the DEV01 server. Is this what you want?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:OSCFv68$HHA.5312@.TK2MSFTNGP02.phx.gbl...
> Huge hurdle cleared. I replaced:
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
> with
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false', @.locallogin = NULL, @.rmtuser
> = 'DBADB_local', @.rmtpassword = 'foobar'
> and it works.
> What else is gonna bite me?
> "Jay" <nospan@.nospam.org> wrote in message news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
>> OK, I cleared the link servers from dev01 & dev03 and ran the following code. But I still can't
>> do a simple select.
>> What I want to do is link the two servers such that only the DBADB_local database can be accessed
>> over the link. The final destination for these links will be between servers that wouldn't
>> otherwise be linked and will include all production, QA and development servers.
>>
>> -- Set a local database
>> -- On DEV03
>> USE DBADB_local
>> /*
>> -- Created a basic local Windows account on DEV03 for DBADB_local
>> -- Delete user (probably best if you comment this out, but I'm using it for testing.
>> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername = 'DBADB_local'
>> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername = 'DBADB_local'
>> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
>> EXEC sp_droplogin @.loginame = 'DBADB_local'
>> */
>> -- Create the user account
>> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb = 'DBADB_local'
>> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername = 'DBADB_local'
>> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get an error about 'Users
>> Collection'
>> -- which I do not understand.
>> -- Reminder, still on DEV03
>> -- Probably comment
>> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
>> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='', @.provider='SQLOLEDB', @.datasrc='DEV01'
>> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' -- tried both
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
>> exec sp_helpserver
>> /* returns
>> DEV01 NULL data access,use remote collation 1 NULL 0 0
>> */
>> -- Move to DEV01
>> -- Create basic local account in Windows.
>> -- Use same code to create the DBADB_local user on DEV01
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>>
>> SELECT * FROM DEV01.DBADB_local.dbo.Servers
>> /* returns
>> Server: Msg 18452, Level 14, State 1, Line 1
>> Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>> */
>|||Ya, I figured that out about 8PM. In this case it's not so bad as the login
is very restricted, but it's still wrong.
I also figured out why it kept failing, I was running under my login, not
the DBADB login. I need to figure out how to switch ID's.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6DE7CE2F-631B-404B-B124-DB44D0C94200@.microsoft.com...
>> What else is gonna bite me?
> A lot. <g>
> Check out the parameter list for sp_addlinkedsrvlogin. You specified NULL
> for @.locallogin. Now *every* local login are mapped to the 'DBADB_local'
> on the DEV01 server. Is this what you want?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:OSCFv68$HHA.5312@.TK2MSFTNGP02.phx.gbl...
>> Huge hurdle cleared. I replaced:
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin =>> 'DBADB_local'
>> with
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false',
>> @.locallogin = NULL, @.rmtuser = 'DBADB_local', @.rmtpassword = 'foobar'
>> and it works.
>> What else is gonna bite me?
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
>> OK, I cleared the link servers from dev01 & dev03 and ran the following
>> code. But I still can't do a simple select.
>> What I want to do is link the two servers such that only the DBADB_local
>> database can be accessed over the link. The final destination for these
>> links will be between servers that wouldn't otherwise be linked and will
>> include all production, QA and development servers.
>>
>> -- Set a local database
>> -- On DEV03
>> USE DBADB_local
>> /*
>> -- Created a basic local Windows account on DEV03 for DBADB_local
>> -- Delete user (probably best if you comment this out, but I'm using it
>> for testing.
>> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername =>> 'DBADB_local'
>> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername =>> 'DBADB_local'
>> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
>> EXEC sp_droplogin @.loginame = 'DBADB_local'
>> */
>> -- Create the user account
>> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb =>> 'DBADB_local'
>> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername =>> 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername =>> 'DBADB_local'
>> -- When accessing the 'Database Access' tab for the DBADB_local DB, I
>> get an error about 'Users Collection'
>> -- which I do not understand.
>> -- Reminder, still on DEV03
>> -- Probably comment
>> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
>> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
>> @.provider='SQLOLEDB', @.datasrc='DEV01'
>> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
>> tried both
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin =>> 'DBADB_local'
>> exec sp_helpserver
>> /* returns
>> DEV01 NULL data access,use remote collation 1 NULL 0 0
>> */
>> -- Move to DEV01
>> -- Create basic local account in Windows.
>> -- Use same code to create the DBADB_local user on DEV01
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>>
>> SELECT * FROM DEV01.DBADB_local.dbo.Servers
>> /* returns
>> Server: Msg 18452, Level 14, State 1, Line 1
>> Login failed for user '(null)'. Reason: Not associated with a trusted
>> SQL Server connection.
>> */
>>
>