servers

Hi,
We have the following code to automatically create the linked server in one
of our upgrade scripts.
declare @.Security_dbname nvarchar(128) -- This is the security
database name
declare @.Security_Server nvarchar(128) -- This is the Security
Server Name
declare @.Instance nvarchar(128) -- This is the
SQL Server instance name
declare @.UserID nvarchar(128) -- This is the
SQL Server administrator user
declare @.Pwd nvarchar(128) -- This is the
SQL Server administrator user's password
/**********Begin User input required section ***************/
-- DCBR 1206
set @.Security_dbname = ''
set @.Security_Server = ''
set @.Instance = ''
set @.UserID = ''
set @.Pwd = ''
/**************End User inputs ****************************/
Create Table ##temp_variables (
Security_dbname nvarchar(128),
Security_Server nvarchar(128),
Instance nvarchar(128),
UserID nvarchar(128),
Pwd nvarchar(128))
Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
@.Instance, @.UserID, @.Pwd)
set @.Security_dbname = (select Security_dbname from ##temp_variables)
set @.Security_Server = (select Security_Server from ##temp_variables)
set @.Instance = (select Instance from ##temp_variables)
set @.UserID = (select UserID from ##temp_variables)
set @.Pwd = (select Pwd from ##temp_variables)
BEGIN
declare @.badded int
set @.badded = 0
if len(@.Security_Server) <> 0
BEGIN
if len(@.Instance) <> 0
BEGIN
declare @.Security_Server2 nvarchar(128)
set @.Security_Server2 = @.Security_Server
set @.Security_Server = @.Security_Server2 + '\' + @.Instance
set @.Instance = @.Security_Server2 + '\' + @.Instance
END
else
BEGIN
set @.Instance = @.Security_Server
END
if not exists(select * from master..sysservers where srvname = @.Security_Server)
BEGIN
EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
set @.badded = 1
END
END
--Making sure user has entered a value for the security database name
IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
BEGIN
RAISERROR('Please enter the security database name',16,1)
END
ELSE
BEGIN
DECLARE @.sUpGradeScript nvarchar(1024)
set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
WindowsAccountID FROM '
IF len(@.Security_Server) <> 0
set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
exec(@.sUpGradeScript)
if @.badded = 1
BEGIN
exec sp_droplinkedsrvlogin @.Security_Server, null
exec sp_dropserver @.Security_Server
END
END
drop table ##temp_variables
END
This code updates a table in a database in one machine with a value from a
column in another table from another database that exists in another machine.
Obviously, we have to create a linked server in order to do this. In trying
to automate this as much as we can with minimal manual user input, we keep
getting the following error:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
What are we doing wrong? How can we get rid of the error?
Thank you in advance,
DeeI would like to add that I've tried creating a client network alias using
both Named Pipes and TCP/IP. In addition, I've used the IP address rather
than the machine name and still no luck.
"bpdee" wrote:
> Hi,
> We have the following code to automatically create the linked server in one
> of our upgrade scripts.
> declare @.Security_dbname nvarchar(128) -- This is the security
> database name
> declare @.Security_Server nvarchar(128) -- This is the Security
> Server Name
> declare @.Instance nvarchar(128) -- This is the
> SQL Server instance name
> declare @.UserID nvarchar(128) -- This is the
> SQL Server administrator user
> declare @.Pwd nvarchar(128) -- This is the
> SQL Server administrator user's password
> /**********Begin User input required section ***************/
> -- DCBR 1206
> set @.Security_dbname = ''
> set @.Security_Server = ''
> set @.Instance = ''
> set @.UserID = ''
> set @.Pwd = ''
> /**************End User inputs ****************************/
>
> Create Table ##temp_variables (
> Security_dbname nvarchar(128),
> Security_Server nvarchar(128),
> Instance nvarchar(128),
> UserID nvarchar(128),
> Pwd nvarchar(128))
> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
> @.Instance, @.UserID, @.Pwd)
> set @.Security_dbname = (select Security_dbname from ##temp_variables)
> set @.Security_Server = (select Security_Server from ##temp_variables)
> set @.Instance = (select Instance from ##temp_variables)
> set @.UserID = (select UserID from ##temp_variables)
> set @.Pwd = (select Pwd from ##temp_variables)
> BEGIN
> declare @.badded int
> set @.badded = 0
> if len(@.Security_Server) <> 0
> BEGIN
> if len(@.Instance) <> 0
> BEGIN
> declare @.Security_Server2 nvarchar(128)
> set @.Security_Server2 = @.Security_Server
> set @.Security_Server = @.Security_Server2 + '\' + @.Instance
> set @.Instance = @.Security_Server2 + '\' + @.Instance
> END
> else
> BEGIN
> set @.Instance = @.Security_Server
> END
> if not exists(select * from master..sysservers where srvname => @.Security_Server)
> BEGIN
> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
> set @.badded = 1
> END
> END
> --Making sure user has entered a value for the security database name
> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
> BEGIN
> RAISERROR('Please enter the security database name',16,1)
> END
> ELSE
> BEGIN
> DECLARE @.sUpGradeScript nvarchar(1024)
> set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
> WindowsAccountID FROM '
> IF len(@.Security_Server) <> 0
> set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
> set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
> exec(@.sUpGradeScript)
> if @.badded = 1
> BEGIN
> exec sp_droplinkedsrvlogin @.Security_Server, null
> exec sp_dropserver @.Security_Server
> END
> END
> drop table ##temp_variables
> END
> This code updates a table in a database in one machine with a value from a
> column in another table from another database that exists in another machine.
> Obviously, we have to create a linked server in order to do this. In trying
> to automate this as much as we can with minimal manual user input, we keep
> getting the following error:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
>
> What are we doing wrong? How can we get rid of the error?
> Thank you in advance,
> Dee|||Looks like a security issue to me. Try doing the basic definitions by hand
to sort out the security issues, then you can sort out the script or its
invocation once you figure the problem.
It's very hard to help with security issues given the information you're
provided.
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E42FA181-4248-4D7D-862D-6C88AE88E8A8@.microsoft.com...
>I would like to add that I've tried creating a client network alias using
> both Named Pipes and TCP/IP. In addition, I've used the IP address rather
> than the machine name and still no luck.
> "bpdee" wrote:
>> Hi,
>> We have the following code to automatically create the linked server in
>> one
>> of our upgrade scripts.
>> declare @.Security_dbname nvarchar(128) -- This is the security
>> database name
>> declare @.Security_Server nvarchar(128) -- This is the Security
>> Server Name
>> declare @.Instance nvarchar(128) -- This is
>> the
>> SQL Server instance name
>> declare @.UserID nvarchar(128) -- This is
>> the
>> SQL Server administrator user
>> declare @.Pwd nvarchar(128) -- This is
>> the
>> SQL Server administrator user's password
>> /**********Begin User input required section ***************/
>> -- DCBR 1206
>> set @.Security_dbname = ''
>> set @.Security_Server = ''
>> set @.Instance = ''
>> set @.UserID = ''
>> set @.Pwd = ''
>> /**************End User inputs ****************************/
>>
>> Create Table ##temp_variables (
>> Security_dbname nvarchar(128),
>> Security_Server nvarchar(128),
>> Instance nvarchar(128),
>> UserID nvarchar(128),
>> Pwd nvarchar(128))
>> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
>> @.Instance, @.UserID, @.Pwd)
>> set @.Security_dbname = (select Security_dbname from ##temp_variables)
>> set @.Security_Server = (select Security_Server from ##temp_variables)
>> set @.Instance = (select Instance from ##temp_variables)
>> set @.UserID = (select UserID from ##temp_variables)
>> set @.Pwd = (select Pwd from ##temp_variables)
>> BEGIN
>> declare @.badded int
>> set @.badded = 0
>> if len(@.Security_Server) <> 0
>> BEGIN
>> if len(@.Instance) <> 0
>> BEGIN
>> declare @.Security_Server2 nvarchar(128)
>> set @.Security_Server2 = @.Security_Server
>> set @.Security_Server = @.Security_Server2 + '\' + @.Instance
>> set @.Instance = @.Security_Server2 + '\' + @.Instance
>> END
>> else
>> BEGIN
>> set @.Instance = @.Security_Server
>> END
>> if not exists(select * from master..sysservers where srvname =>> @.Security_Server)
>> BEGIN
>> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
>> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
>> set @.badded = 1
>> END
>> END
>> --Making sure user has entered a value for the security database name
>> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
>> BEGIN
>> RAISERROR('Please enter the security database name',16,1)
>> END
>> ELSE
>> BEGIN
>> DECLARE @.sUpGradeScript nvarchar(1024)
>> set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
>> WindowsAccountID FROM '
>> IF len(@.Security_Server) <> 0
>> set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' +
>> '.'
>> set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
>> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
>> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
>> exec(@.sUpGradeScript)
>> if @.badded = 1
>> BEGIN
>> exec sp_droplinkedsrvlogin @.Security_Server, null
>> exec sp_dropserver @.Security_Server
>> END
>> END
>> drop table ##temp_variables
>> END
>> This code updates a table in a database in one machine with a value from
>> a
>> column in another table from another database that exists in another
>> machine.
>> Obviously, we have to create a linked server in order to do this. In
>> trying
>> to automate this as much as we can with minimal manual user input, we
>> keep
>> getting the following error:
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>>
>> What are we doing wrong? How can we get rid of the error?
>> Thank you in advance,
>> Dee