Cannot enlist with MSX server

We have a server with the default instance and 2 additional named instances running SQL 2000 SP4. Recently I installed another instance for SQL Server 2005. We also use MSX/TSX approach to manage the servers so I tried to enlist the SQL 2005 instance with the master server using the

msdb..sp_msx_enlist @.msx_server_name = N'SQLMON', @.location = N'Datacentre. Margin Lending overnight copy'

and got this error,

Msg 22026, Level 16, State 1, Line 0

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'SQLMON'.)

Looking at the text for this procedure it looks like it reads the wrong registry keys. The procedure it hardcoded to read,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent

This key doesn't exist, but this one does,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent

Is this causing the connection error? I know that the MSX server is available as I can connect and query it in Management Studio.

http://msdn2.microsoft.com/en-us/library/ms365379.aspx

Please take a look at the above link on what has changed in MSX/TSX on security lines.

Also, note that in a mixed(SQL 2K and 2005) multi-server environment MSX can be only SQL 2000. TSXs can be both SQL 2K and 2005

|||

Thanks for your reply. I set the Encryption option to zero.and was able to make the SQL 2005 server a TSX.

When will the encryption option be selecteable in the TSX setup wizard?

|||Unfortunately no. We have a tracking bug for the next release to expose it at SMO and UI level

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot create a connection to data source

I installed the SQL Server 2005 June CTP. I created a datasource named Datawarhouse. I went into the Report Builder and created a report and could actually run the report successfully. When I saved the report & ran it I got the following error: An error has occ

urred during report processing.

Cannot create a connection to data source 'Datawarehouse'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors. I am not sure what I am doing wrong. Why don't it find the data source? Any help would be appreciated.

Thanks! JohnAre you using stored credentials for the connection or integrated security? If you try to run the report on the server locally, you should get more information about the error.

Cannot connect with Enterprise Manager

I am attempting to connect to a named instance of SS 2000 across the
Internet using Enterprise Manager. The machine running SS is behind a NAT
router. According to BOL I should be able to connect if I forward the TCP
port that SS is using (1061 in my case) and UDP port 1434 to the SS
machine. However, I cannot get this to work.
When I attempt to register the server in Enterprise Manager I use the WAN
IP address of the router as the server name. However, registration fails
with the message "SQL Server does not exist or access denied."
I cannot believe that I am the only person trying to do this. Can someone
tell me what I am missing?
Bill Todd
Bill Todd wrote:
> I am attempting to connect to a named instance of SS 2000 across the
> Internet using Enterprise Manager. The machine running SS is behind a
> NAT router. According to BOL I should be able to connect if I forward
> the TCP port that SS is using (1061 in my case) and UDP port 1434 to
> the SS machine. However, I cannot get this to work.
> When I attempt to register the server in Enterprise Manager I use the
> WAN IP address of the router as the server name. However,
> registration fails with the message "SQL Server does not exist or
> access denied."
> I cannot believe that I am the only person trying to do this. Can
> someone tell me what I am missing?
Do you have the Windows Filewall or another software firewall running on
the PC? If so, you may have to open up those ports on the PC.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for replying. SQL Server is running a a Win2k Server machine with
no software firewall. The only firewall is the NAT router.
Bill Todd
David Gugick wrote:

> Do you have the Windows Filewall or another software firewall running on
the PC? If so, you may have to open up those ports on the PC.
|||I should also mention that I have no problem connecting to SQL server
across the LAN that the server machine is on.
Bill Todd
|||On 2005-01-11, Bill Todd <no@.no.com> wrote:
> I should also mention that I have no problem connecting to SQL server
> across the LAN that the server machine is on.
>
If I got you right, you have a situation similair to this:
You have an SQL server in your local network, uses local IP adress pool
(192.168.x.x, or 10.x.x.x or similair), and if that machine wants to go out,
it uses default gateway set up on your network. I'm presuming that NAT is
running on that gateway. So, if you're connecting to host outside your
network, from any of the machines inside your network, the outside host will
'see' the gateway IP address (public one, not the private one). So, there is
no way you'd be able to connect to the inside host.
You can do what BOL suggests, do a port forwarding on your gateway. You
don't need UDP ports, TCP only matters. So, for a default instalation, where
SQL server 'listens' on port 1433, you'd have to choose a port on the
gateway, and forward all incomming connections from that port to the 1433
port on the machine where the SQL server is.
Or, you could use ssh, if you have access to the 'outside' ssh server to
establish ssh tunnels, and then set up port forwarding directly from your
machine to the SQL server in your network. I'd go with SSH, because of the
security level it offers. You'd be having all communication between your SQL
server and you as a client - encrtypted.
I'm using SSH to connect to my work network from any dialup location in the
world. And it works excellent.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Your understanding is exactly right except that the gateway on the network
is a simple Linksys cable/DSL router. The other difference is that I am
trying to connect to a named SS 2000 installation which, according to
Enterprise Manager, uses port 1061.
I have, I think, done exactly what you suggest. I have forwarded TCP port
1061 on the Internet side of the router to port 1061 on the machine
running SQL server. It does not work.
I have no idea how to implement SSH and security is not an issue in this
case since the remote adminstration will be temporary and the only data
exposed will be test data.
Clearly I am missing something. Is there anything else you can think of
that I should check or set? This should be easy.
Bill Todd
Mario Splivalo wrote:

> On 2005-01-11, Bill Todd <no@.no.com> wrote:
> If I got you right, you have a situation similair to this:
> You have an SQL server in your local network, uses local IP adress pool
> (192.168.x.x, or 10.x.x.x or similair), and if that machine wants to go
out,
> it uses default gateway set up on your network. I'm presuming that NAT is
> running on that gateway. So, if you're connecting to host outside your
> network, from any of the machines inside your network, the outside host
will
> 'see' the gateway IP address (public one, not the private one). So,
there is
> no way you'd be able to connect to the inside host.
> You can do what BOL suggests, do a port forwarding on your gateway. You
> don't need UDP ports, TCP only matters. So, for a default instalation,
where
> SQL server 'listens' on port 1433, you'd have to choose a port on the
> gateway, and forward all incomming connections from that port to the 1433
> port on the machine where the SQL server is.
> Or, you could use ssh, if you have access to the 'outside' ssh server to
> establish ssh tunnels, and then set up port forwarding directly from your
> machine to the SQL server in your network. I'd go with SSH, because of
the
> security level it offers. You'd be having all communication between your
SQL
> server and you as a client - encrtypted.
> I'm using SSH to connect to my work network from any dialup location in
the
> world. And it works excellent.
> Mike
|||More information. The problem is connecting to a named instance. SS 2000
is installed as a named instance because the server is also running SS 7.
I _can_ connect through the NAT router to SQL Server 7 using the steps you
described. So, the question is, what do I have to do differently to
connect to the named instance of SS 2000 that is litening on port 1061.
Port 1061 is forwarded exactly like 1433.
I suspect that I need to change some setting on the client machine to get
Enterprise Manager to connect on port 1061 instead of 1433. Any
suggestions?
Bill Todd
|||Bill Todd wrote:
> More information. The problem is connecting to a named instance. SS
> 2000 is installed as a named instance because the server is also
> running SS 7. I _can_ connect through the NAT router to SQL Server 7
> using the steps you described. So, the question is, what do I have to
> do differently to connect to the named instance of SS 2000 that is
> litening on port 1061. Port 1061 is forwarded exactly like 1433.
> I suspect that I need to change some setting on the client machine to
> get Enterprise Manager to connect on port 1061 instead of 1433. Any
> suggestions?
Go into the SQL Server Server Network Utility on the server and assign
the named instance a hard-coded port. With named instances, SQL Server
pulls from a pool and there is no guarantee you'll get the same port
every time the instance is started. You also need to specify the port
when connecting by specifying the SERVER,PORT (e.g. MyServer,1434). You
could also assign an alias from the remote PC to the server to avoid
having to type the server\instance long name and port # by assigning the
instance a simple name, TCP-IP as the protocol, and put in the port
number. Then you use the alias from the remote PC when connecting.
Just make sure you have port forwarding turned on for the assigned TCP
port number on the router.
David Gugick
Imceda Software
www.imceda.com
|||The problem was the comma between the server IP address and the port
number. I found an article on MSN that showed a space between the server
and port. I also tried a colon.
The devil truly is in the details. Thanks.
Bill Todd

Cannot connect via Linked Server

We have two servers, each with named instances of SQL Server 2000 SP3a installed. Server1\Instance1 has a linked server to Server2\Instance2. The linked server is using the SQLOLEODB provider. This linked server is setup with remote login and a correct
password. We get an error #17: SQL Server does not exist or access denied. We also get the same behaviour while trying an OPENROWSET query from the Server1\Instance1 Query Analyzer. All other SQL Server instances we have installed (on other machines)
can connect to Server2\Instance2 with a SQLOLEDB linked server, no problems.
As far as I can tell, this error is only happening when using the SQL Server. I can connect to the remote (Server2\Instance2) server using osql from Server1, and I can even connect to Server2\Instance2 by using a SQLOLEDB connection from a desktop app (f
or example, I can test the connection successfully in a UDL using SQLOLEDB to the remote server from Server1).
We are only using named pipes. I have verified that all client and all server network utilities are set exclusively to named pipes. This is true. I have also performed a network trace on the Server1 server. It does not even attempt to contact the Serv
er2 machine. I have also network traced a different SQL Server instance on another machine that can connect to Server2\Instance2 and it's network trace shows a very clear, immediate connection to the Server2.
What other steps can I take to troubleshoot this problem? I think that I have hit all the common trouble points in setting up a linked server.
Thanks,
Frank
Update: We noticed that there was an additional NIC card on the Server1 server that had an APIPA IP address. We have disabled this NIC and now the network trace is showing some communication between Server1 and Server2.
Basically, the trace shows Server1 attempts to connect using SMB to set the session up on Server2. Server2 responds with an NT error 22, STATUS_MORE_PROCESSING_REQUIRED, then a few frames later, it tries to open the IPC$ pipe on Server2. Server2 respond
s with an NT error 34, STATUS_ACCESS_DENIED. So why is SMB not allowing connections to the IPC$ pipe? I can log in to the Server1 machine under the same account as SQL Server is running as and issue a successful "net use \\Server2\IPC$" without a proble
m. The network trace requests from Server1 for the net use look similar to those generated by SQL Server linked server. Why does one get an access denied and the other does not?
Thanks,
Frank
|||Hi Frank,
I learned you are failed to create linked Server between Server1\Instance1
and Server2\Instance2. The error "SQL Server does not exist or access
denied." occurred.
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
2. Please check to see if UDP port 1434 is enabled.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Yuan, I have completed the following steps for troubleshooting from your post. My comments are indented below each of your suggestions:
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
I have checked the Server & Client network utilities: named pipes is default and the only protocol. That was stated in my original post.
2. Please check to see if UDP port 1434 is enabled.
Yes, port 1434 UDP is listening on the Server2 machine.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
Adding the linked server is not the problem. I can do it either through Enterprise Manager or the stored procedure. It is when I try to connect to the linked server (for example, when EM queries for table names from the linked server) that I get
Error #17 SQL Server does not exist or access denied, as stated in my original post.
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
I have been monitoring the event log (all application, security and system) for any errors on either system and there is nothing.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
I have tried to create an alias with the same results. No connectivity; access denied.
I have hit most of these normal troubleshooting steps. I don't usually jump to a network trace before trying things like this. The SMB network trace for opening the named pipe shows an access denied error from the Server2. What other issues might cause
this?
Thanks,
Frank
|||Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried creating a SQL Server without using the OLEDB provider and this failed in the same manner as the prior failures. Both the openquery and the four part fully qualified name queries failed with Error #17, SQL Server does not exist or acc
ess denied.
The MDAC is 2.7SP1 on Server1, 2.8 on Server2...
Any help at this point would be greatly appreciated...
Thanks,
Frank
-- "Yuan Shao" wrote: --
Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>?X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Frank,
Thanks for your feedback. After reviewing our discussion in detail, I
noticed you test the connection successfully in a UDL using SQLOLEDB to the
remote server (server2/instance2) from Server1. However, you still got the
error "SQL Server does not exist or access denied." using linked server
I would like you to check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists. I have attached a linkedserver.zip file with my notification to
help describe the configuration of linked server using sa login.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried connecting to the linked server using the sa password and it still results in the original failure: Error 17, SQL Server does not exist or access denied. It seems like it is the named pipes connectivity shutting the door down rather t
han SQL Server, although I cannot verify this as neither SQL Server nor the operating system are logging this (despite having all failure logging turned on both the OS local security policy and in SQL Server). Any other ideas?
Thanks,
Frank
|||Have you tried a connection method other than named pipes. Try making TcpIP and secondary connection method and setting up an alias using IP.
Are both servers on the same segment?
Sometimes netbios names are not passed across segments.
Have you tried rebooting the server1 since disabling the NIC. Sometimes it needs to rebroadcast its existance on the NIC.
Thanks
Jeff
|||Hello Frank,
Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| Thread-Topic: Cannot connect via Linked Server
| thread-index: AcQh0Df+wj3plzMnQ3+eAqcmfTrgkg==
| X-WN-Post: microsoft.public.sqlserver.connect
| From: "=?Utf-8?B?RnJhbmsgSm9uZXM=?=" <frank@.bogusfiosincfake.com>
| References: <99E987D2-0636-4917-8627-8E1A87789198@.microsoft.com>
<4B4AE818-15A4-4C50-97DC-7370D8DC8D49@.microsoft.com>
<3tFzedKHEHA.612@.cpmsftngxa06.phx.gbl>
| Subject: RE: Cannot connect via Linked Server
| Date: Tue, 13 Apr 2004 20:26:04 -0700
| Lines: 4
| Message-ID: <50E78236-79B1-4D3F-9BFE-CB02692DB966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40978
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Yuan, we have tried connecting to the linked server using the sa password
and it still results in the original failure: Error 17, SQL Server does not
exist or access denied. It seems like it is the named pipes connectivity
shutting the door down rather than SQL Server, although I cannot verify
this as neither SQL Server nor the operating system are logging this
(despite having all failure logging turned on both the OS local security
policy and in SQL Server). Any other ideas?
Thanks,
Frank
|

Cannot connect via Linked Server

We have two servers, each with named instances of SQL Server 2000 SP3a insta
lled. Server1\Instance1 has a linked server to Server2\Instance2. The link
ed server is using the SQLOLEODB provider. This linked server is setup with
remote login and a correct
password. We get an error #17: SQL Server does not exist or access denied.
We also get the same behaviour while trying an OPENROWSET query from the Se
rver1\Instance1 Query Analyzer. All other SQL Server instances we have inst
alled (on other machines)
can connect to Server2\Instance2 with a SQLOLEDB linked server, no problems.
As far as I can tell, this error is only happening when using the SQL Server
. I can connect to the remote (Server2\Instance2) server using osql from Se
rver1, and I can even connect to Server2\Instance2 by using a SQLOLEDB conne
ction from a desktop app (f
or example, I can test the connection successfully in a UDL using SQLOLEDB t
o the remote server from Server1).
We are only using named pipes. I have verified that all client and all serv
er network utilities are set exclusively to named pipes. This is true. I h
ave also performed a network trace on the Server1 server. It does not even
attempt to contact the Serv
er2 machine. I have also network traced a different SQL Server instance on
another machine that can connect to Server2\Instance2 and it's network trace
shows a very clear, immediate connection to the Server2.
What other steps can I take to troubleshoot this problem? I think that I ha
ve hit all the common trouble points in setting up a linked server.
Thanks,
FrankUpdate: We noticed that there was an additional NIC card on the Server1 serv
er that had an APIPA IP address. We have disabled this NIC and now the netw
ork trace is showing some communication between Server1 and Server2.
Basically, the trace shows Server1 attempts to connect using SMB to set the
session up on Server2. Server2 responds with an NT error 22, STATUS_MORE_PR
OCESSING_REQUIRED, then a few frames later, it tries to open the IPC$ pipe o
n Server2. Server2 respond
s with an NT error 34, STATUS_ACCESS_DENIED. So why is SMB not allowing con
nections to the IPC$ pipe? I can log in to the Server1 machine under the sa
me account as SQL Server is running as and issue a successful "net use \\Ser
ver2\IPC$" without a proble
m. The network trace requests from Server1 for the net use look similar to
those generated by SQL Server linked server. Why does one get an access den
ied and the other does not?
Thanks,
Frank|||Hi Frank,
I learned you are failed to create linked Server between Server1\Instance1
and Server2\Instance2. The error "SQL Server does not exist or access
denied." occurred.
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
2. Please check to see if UDP port 1434 is enabled.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Yuan, I have completed the following steps for troubleshooting from your
post. My comments are indented below each of your suggestions:
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
I have checked the Server & Client network utilities: named pipes is default
and the only protocol. That was stated in my original post.
2. Please check to see if UDP port 1434 is enabled.
Yes, port 1434 UDP is listening on the Server2 machine.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
Adding the linked server is not the problem. I can do it either through Ent
erprise Manager or the stored procedure. It is when I try to connect to the
linked server (for example, when EM queries for table names from the linked
server) that I get
Error #17 SQL Server does not exist or access denied, as stated in my origin
al post.
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
I have been monitoring the event log (all application, security and system)
for any errors on either system and there is nothing.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
I have tried to create an alias with the same results. No connectivity; acc
ess denied.
I have hit most of these normal troubleshooting steps. I don't usually jump
to a network trace before trying things like this. The SMB network trace f
or opening the named pipe shows an access denied error from the Server2. Wh
at other issues might cause
this?
Thanks,
Frank|||Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>_<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/defaul...kb;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Yuan, we have tried creating a SQL Server without using the OLEDB provider a
nd this failed in the same manner as the prior failures. Both the openquery
and the four part fully qualified name queries failed with Error #17, SQL S
erver does not exist or acc
ess denied.
The MDAC is 2.7SP1 on Server1, 2.8 on Server2...
Any help at this point would be greatly appreciated...
Thanks,
Frank
-- "Yuan Shao" wrote: --
Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>?_<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/defaul...kb;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Frank,
Thanks for your feedback. After reviewing our discussion in detail, I
noticed you test the connection successfully in a UDL using SQLOLEDB to the
remote server (server2/instance2) from Server1. However, you still got the
error "SQL Server does not exist or access denied." using linked server
I would like you to check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists. I have attached a linkedserver.zip file with my notification to
help describe the configuration of linked server using sa login.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Yuan, we have tried connecting to the linked server using the sa password an
d it still results in the original failure: Error 17, SQL Server does not ex
ist or access denied. It seems like it is the named pipes connectivity shut
ting the door down rather t
han SQL Server, although I cannot verify this as neither SQL Server nor the
operating system are logging this (despite having all failure logging turned
on both the OS local security policy and in SQL Server). Any other ideas?
Thanks,
Frank|||Have you tried a connection method other than named pipes. Try making TcpIP
and secondary connection method and setting up an alias using IP.
Are both servers on the same segment?
Sometimes netbios names are not passed across segments.
Have you tried rebooting the server1 since disabling the NIC. Sometimes it
needs to rebroadcast its existance on the NIC.
Thanks
Jeff|||Hello Frank,
Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| Thread-Topic: Cannot connect via Linked Server
| thread-index: AcQh0Df+wj3plzMnQ3+eAqcmfTrgkg==
| X-WN-Post: microsoft.public.sqlserver.connect
| From: "examnotes" <frank@.bogusfiosincfake.com>
| References: <99E987D2-0636-4917-8627-8E1A87789198@.microsoft.com>
<4B4AE818-15A4-4C50-97DC-7370D8DC8D49@.microsoft.com>
<3tFzedKHEHA.612@.cpmsftngxa06.phx.gbl>
| Subject: RE: Cannot connect via Linked Server
| Date: Tue, 13 Apr 2004 20:26:04 -0700
| Lines: 4
| Message-ID: <50E78236-79B1-4D3F-9BFE-CB02692DB966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40978
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Yuan, we have tried connecting to the linked server using the sa password
and it still results in the original failure: Error 17, SQL Server does not
exist or access denied. It seems like it is the named pipes connectivity
shutting the door down rather than SQL Server, although I cannot verify
this as neither SQL Server nor the operating system are logging this
(despite having all failure logging turned on both the OS local security
policy and in SQL Server). Any other ideas?
Thanks,
Frank
|

Cannot Connect using IP to Clustered Named Instance

I am having trouble connecting when using the IP address to a named
instanced using dynamic port assignments and hosted on a 2K3 cluster (even
when at the console on the node hosting the SQL instance). I receive "SQL
Server does not exist or access denied". There are no entries created in
the event log. I am able to connect with no problems using the instance
name. I'm attempting to connect usingQuery Analyzer. Any ideas?
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
Hi Michael,
In order to connect to a Named Instance you need to either:
a. Supply the Servername\InstanceName
b. Supply the Servername, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||It seems that you have trouble connecting through TCP/IP. Verify in event log that SQL is listening on TCPIP and not only on Shared Memory / Named Pipes.
To verify that this is a problem from QueryAnalyzer try to connect with tcp:ServerName\InstanceName. If it isn't work then server is not listening on TCP at all...
post back after this and we will see what then...
Rgds
Sinisa Perovic
|||Thanks for the help.
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:51hmVwbIEHA.3636@.cpmsftngxa06.phx.gbl...
> Hi Michael,
> In order to connect to a Named Instance you need to either:
> a. Supply the Servername\InstanceName
> b. Supply the Servername, port
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

Cannot connect to to the other server when setting up replication in SQL 2005

Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.

SQL1\MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2\MSSQL01 as a subscriber the connection fails to the remote server.

The error message is:

TITLE: Connect to Server

Cannot connect to SQL2\MSSQL01.

ADDITIONAL INFORMATION:

Failed to connect to server SQL2\MSSQL01. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2\MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.

When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.

TITLE: Connect to Server

Cannot connect to TFMHQARNDE07.

ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ' SQL2\MSSQL01'. (Replication.Utilities)

I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.

When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.

Any one have any idea what it can be?

I found the problem. I didnt have the SQL Browser service runnning which looks to be needed when using instances. After starting it, it works to connect.

Cannot connect to SS 2000 across Internet

I am attempting to connect to a named instance of SS 2000 across the
Internet using Enterprise Manager. The machine running SS is behind a NAT
router. According to BOL I should be able to connect if I forward the TCP
port that SS is using (1061 in my case) and UDP port 1434 to the SS
machine. However, I cannot get this to work.
When I attempt to register the server in Enterprise Manager I use the WAN
IP address of the router as the server name. However, registration fails
with the message "SQL Server does not exist or access denied."
I cannot believe that I am the only person trying to do this. Can someone
tell me what I am missing?
Bill Todd
Bill Todd wrote:
> I am attempting to connect to a named instance of SS 2000 across the
> Internet using Enterprise Manager. The machine running SS is behind a
> NAT router. According to BOL I should be able to connect if I forward
> the TCP port that SS is using (1061 in my case) and UDP port 1434 to
> the SS machine. However, I cannot get this to work.
> When I attempt to register the server in Enterprise Manager I use the
> WAN IP address of the router as the server name. However,
> registration fails with the message "SQL Server does not exist or
> access denied."
> I cannot believe that I am the only person trying to do this. Can
> someone tell me what I am missing?
I believe this was addressed in the other ng.
David Gugick
Imceda Software
www.imceda.com

Cannot Connect to SQL Server Analysis Services - 2005

Help please!!! I tried everything I can think of - several re-installs, but I still can't connect to Analysis Services (named or default).

My SQL is installed on XP Pro. I get the following error message:

TITLE: Connect to Server

Cannot connect to RBM.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

No connection could be made because the target machine actively refused it (System)

BUTTONS:

OK

===================================

Cannot connect to RBM.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

No connection could be made because the target machine actively refused it (System)

Program Location:

at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.Connect(EndPoint remoteEP)
at System.Net.Sockets.TcpClient.Connect(IPEndPoint remoteEP)
at System.Net.Sockets.TcpClient.Connect(IPAddress address, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClientByAddress(String hostName, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

What am I doing wrong? Does anyone have the answer? Microsoft? What is wrong? Help please!! - Hope your listenning to all the frustrations. I never had these issues with SQL 2000 - Analysis Services worked just terrific.

I need to get this working soon. Any help will be greatly appreciated.

Thanks in advance to everyone.

Start by disabling the XP firewall. I have seen this message before when talking to the wrong port, or hitting SSAS 2000.

If this works you can configure the XP fiewwall to only have the relevant port open.

cannot connect to sql 2005 with management studio

Hi ,

I install sql 2005 CTP but cannot connect to the server with management studio

installation was successful and was named instance and under local system account

in event viewer mssql service is running ok, I see even it connects to master db

I can start and stop server with my old sql agent 2000

and I see only 1 error saying in event log:

The SQL Network Interface library was unable to register SPN. Error: 0x54b.

I have for OS 2003 server running on my laptop :-)

any help?

please read following forum thread.
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=20067
hope that solves your problem.
thanks
sql blog
|||thanks just ZoneAlarm firewall was too good - it works fine now

Cannot connect to second cluster node w/instance name

I have an active/active SQL2005 cluster. Node1 has a default instance of
SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
without problem. When I attempt to connect to node2 remotely I get an error
basically stating "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)".
When I connect I am using node2\sqlb as the connection name--this should
give me access to the named instance. I can however access node2\sqlb from
node2 directly (locally on the server). Yes, I have made sure named pipes
and tcp/ip are enabled. Made sure no firewalls, etc. The weird thing is
that I was able to make a remote connection for a short time (like two
hours) and then I couldn't anymore.
I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
console installed locally. Any information would be greatly appreciated.
Is the SQL Server Browser Service running to provide the tcp port number for
the named instance?
If not, what port is the named instance listening on? Can you connect to
node2,<port number> ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPerkins" <joshp@.CUIAB.LOCAL> wrote in message
news:DBDEDBC3-AEF7-48E0-91D7-25977A8AEBB9@.microsoft.com...
>I have an active/active SQL2005 cluster. Node1 has a default instance of
>SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
>SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
>without problem. When I attempt to connect to node2 remotely I get an
>error basically stating "An error has occurred while establishing a
>connection to the server. When connecting to SQL Server 2005, this failure
>may be caused by the fact that under the default settings SQL Server does
>not allow remote connections. (provider: SQL Network Interfaces, error:
>26 - Error Locating Server/Instance Specified)".
> When I connect I am using node2\sqlb as the connection name--this should
> give me access to the named instance. I can however access node2\sqlb
> from node2 directly (locally on the server). Yes, I have made sure named
> pipes and tcp/ip are enabled. Made sure no firewalls, etc. The weird
> thing is that I was able to make a remote connection for a short time
> (like two hours) and then I couldn't anymore.
> I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
> console installed locally. Any information would be greatly appreciated.

Cannot connect to second cluster node w/instance name

I have an active/active SQL2005 cluster. Node1 has a default instance of
SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
without problem. When I attempt to connect to node2 remotely I get an error
basically stating "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)".
When I connect I am using node2\sqlb as the connection name--this should
give me access to the named instance. I can however access node2\sqlb from
node2 directly (locally on the server). Yes, I have made sure named pipes
and tcp/ip are enabled. Made sure no firewalls, etc. The weird thing is
that I was able to make a remote connection for a short time (like two
hours) and then I couldn't anymore.
I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
console installed locally. Any information would be greatly appreciated.Is the SQL Server Browser Service running to provide the tcp port number for
the named instance?
If not, what port is the named instance listening on? Can you connect to
node2,<port number> ?
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPerkins" <joshp@.CUIAB.LOCAL> wrote in message
news:DBDEDBC3-AEF7-48E0-91D7-25977A8AEBB9@.microsoft.com...
>I have an active/active SQL2005 cluster. Node1 has a default instance of
>SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
>SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
>without problem. When I attempt to connect to node2 remotely I get an
>error basically stating "An error has occurred while establishing a
>connection to the server. When connecting to SQL Server 2005, this failure
>may be caused by the fact that under the default settings SQL Server does
>not allow remote connections. (provider: SQL Network Interfaces, error:
>26 - Error Locating Server/Instance Specified)".
> When I connect I am using node2\sqlb as the connection name--this should
> give me access to the named instance. I can however access node2\sqlb
> from node2 directly (locally on the server). Yes, I have made sure named
> pipes and tcp/ip are enabled. Made sure no firewalls, etc. The weird
> thing is that I was able to make a remote connection for a short time
> (like two hours) and then I couldn't anymore.
> I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
> console installed locally. Any information would be greatly appreciated.

Cannot connect to remote SSRS instance with SSMS

I recent installed a named instance of SSRS 2005 on a remote server. The server has a side by side install of SSRS 2000 and SSRS 2005, with the SSRS 2005 being a named instance. The server also has SSL and MS Sharepoint Portal Server installed.

When I try and connect to the SSRS 2005 instance with my client install of Mangement Studio, I get the error message:

"The underlying connection was closed: Could not establish a trust relationship for the SSL/TLS secure channel. (System.Web.Services)

Additional Information: The remote certificate is invalid according to the validation procedure. (System)."

Obviously, this is a SLL issue, but, not being a security guru, I have no clue on how to fix this. I do know the certificate is valid, not expired and properly working. Both installs of SSRS are set up using the SSL certificate. Also, I am not connecting to SSIS with Management Studio, as I heard there is a bug connecting to both SSRS and SSIS at the same time.

Anyone have any ideas?

Thanks!!

Jeff

Moving to the reporting services forum|||

Can you connect to https://<CN name in cert>/reportserver$instancename locally or remotely? Make sure you have correctly set the report server urls in your report server and manager config files. The urls should be in the same format.

http://blogs.msdn.com/bimusings/archive/2006/03/06/544856.aspx#621163

http://msdn2.microsoft.com/en-us/library/ms188133(SQL.90).aspx

|||

Yes, I have the URLs set correctly in the config files.

I am not sure how your reply has anything to do with connecting to the reporting server with SSMS. I can see where your answer will affect connecting with Internet Explorer, but not SSMS.

Can anyone else help please?

Thanks,

Jeff

Cannot connect to remote SSRS instance with SSMS

I recent installed a named instance of SSRS 2005 on a remote server. The server has a side by side install of SSRS 2000 and SSRS 2005, with the SSRS 2005 being a named instance. The server also has SSL and MS Sharepoint Portal Server installed.

When I try and connect to the SSRS 2005 instance with my client install of Mangement Studio, I get the error message:

"The underlying connection was closed: Could not establish a trust relationship for the SSL/TLS secure channel. (System.Web.Services)

Additional Information: The remote certificate is invalid according to the validation procedure. (System)."

Obviously, this is a SLL issue, but, not being a security guru, I have no clue on how to fix this. I do know the certificate is valid, not expired and properly working. Both installs of SSRS are set up using the SSL certificate. Also, I am not connecting to SSIS with Management Studio, as I heard there is a bug connecting to both SSRS and SSIS at the same time.

Anyone have any ideas?

Thanks!!

Jeff

Moving to the reporting services forum|||

Can you connect to https://<CN name in cert>/reportserver$instancename locally or remotely? Make sure you have correctly set the report server urls in your report server and manager config files. The urls should be in the same format.

http://blogs.msdn.com/bimusings/archive/2006/03/06/544856.aspx#621163

http://msdn2.microsoft.com/en-us/library/ms188133(SQL.90).aspx

|||

Yes, I have the URLs set correctly in the config files.

I am not sure how your reply has anything to do with connecting to the reporting server with SSMS. I can see where your answer will affect connecting with Internet Explorer, but not SSMS.

Can anyone else help please?

Thanks,

Jeff

Cannot connect to Remote SQL Server Named Instance

Hi...
On a remote server, I have SQL 2005 installed as the default instance as
well as a new named instance.
From SQLCMD, I can connect to both instances. However, from Management
Studio, I can only connect to the default instance. Is there something
special I have to do to connect to the named instance? I try connecting
using:
[My-Server-Name]/InstanceName
Any ideas? I get the "error locating server/instance specified" generic
error.
Thanks![/] is also not allowed for a servername -the instance is part of the na
me.
So put the square brackets around the entire [server/instance] name.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:DE18DCC1-CA47-4066-8E34-2ED8250A9DD5@.microsoft.com...
> Hi...
> On a remote server, I have SQL 2005 installed as the default instance as
> well as a new named instance.
> From SQLCMD, I can connect to both instances. However, from Management
> Studio, I can only connect to the default instance. Is there something
> special I have to do to connect to the named instance? I try connecting
> using:
> [My-Server-Name]/InstanceName
> Any ideas? I get the "error locating server/instance specified" generic
> error.
> Thanks!|||Use SQL Server Configuration Manager and for the Services,
make sure the SQL Browser Service is running.
-Sue
On Thu, 10 Aug 2006 09:06:01 -0700, Forch
<Forch@.discussions.microsoft.com> wrote:

>Hi...
>On a remote server, I have SQL 2005 installed as the default instance as
>well as a new named instance.
>From SQLCMD, I can connect to both instances. However, from Management
>Studio, I can only connect to the default instance. Is there something
>special I have to do to connect to the named instance? I try connecting
>using:
>[My-Server-Name]/InstanceName
>Any ideas? I get the "error locating server/instance specified" generic
>error.
>Thanks!|||Thanks for the suggestion. Unfortunately it didn't work...
The name without the square brackets works with Management Studio locally.
Do you have any other ideas?
Thanks,
Mark
"Arnie Rowland" wrote:

> [/] is also not allowed for a servername -the instance is part of the
name.
> So put the square brackets around the entire [server/instance] name.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Forch" <Forch@.discussions.microsoft.com> wrote in message
> news:DE18DCC1-CA47-4066-8E34-2ED8250A9DD5@.microsoft.com...
>
>

Cannot connect to Named Instance of MSDE through Enterprise Mananger...

Hi- I am hoping someone can help me or shed some light on an issue I am having.

I have recently moved to a dedicated server with MSDE installed on it. The server was installed as the default instance and I changed the password to the SA account. I then copied my entire SA database over to the new MSDE database and everything was working good so it seemed. I believe at some point during copying of the data, or after installalling an application the SA account begain to fail and the password was changed. I have no idea how this happened, and better yet when I tried to change it logged in as the administrator with OSQL it told me I did not have sysadmin rights (I tried multiple Windows NT Auth logins and still got this error trying to change the password).

So... I reinstalled MSDE and created a named instane. I then attached the mdf/ldf files to my named instance and am able to connect ok from a .NET application directly to my new MSDE Named Instance database. Sounds like everything is great right? Not really, I can't figure out how to connect through enterprise manager and its driving me CRAZY!

1. I CAN connect via any .NET app and a connection string

2. I CAN connect via the server with OSQL

3. I CAN connect via the web based enterprise manager (which is very limited and a freeware ASP app).

4. I CANNOT connect via enterprise manager. I have verified from the (SVRNETCN.EXE utility) that the port is open and have tried connecting to MY_IPADDRESS\NAMEDINSTANCE\ but no luck.

One additional side note, when I wasn't using a named instance I had no problems connecting through enterprise manager to my default MSDE instance.

Any help would be grealy appriciated, this is driving me nuts!

-Chad

Ok, I got this figured out. For anyone else that has this issue here is what solved my problem.

Within the client utility create an alias named YourDomainOrIPAddress\NamedInstance and make sure to open up the TCP port with the SVRNETCN.EXE on the server. Also, enabled TCP/IP on the client utility and make sure its the same port.

Additionally within Enterprise Manager I kept trying YourDomainOrIPAdress\NamedInstance:Port# instead what you need to do is try YourDomainOrIPAddress,Port# (Common instead of semi colon).

-Chad

cannot connect to named instance of msde

I installed a named instance of MSDE SP4. It seemed to go OK, and no errors
were reported.
The instance is listed in the computer's services, and it is running.
However, neither the computer name nor the new MSDE instance is listed in
the SQL Server Service Manager (although an instance on a networked computer
is listed). And I cannot connect via osql.
Any suggestions?
hi Paul,
Paul Pedersen wrote:
> I installed a named instance of MSDE SP4. It seemed to go OK, and no
> errors were reported.
> The instance is listed in the computer's services, and it is running.
> However, neither the computer name nor the new MSDE instance is
> listed in the SQL Server Service Manager (although an instance on a
> networked computer is listed). And I cannot connect via osql.
> Any suggestions?
if you can not connect from remote client, it can be depending on disabled
network protocols (default for MSDE installation, you can bypass at install
time providing the DISABLENETWORKPROTOCOLS=0 paramenter or later, at run
time, using the Server Network Utility [svrnetcn.exe] to enable the
desired/required network protocol) or, for instance, firewall/network
problems (Windows XP sp2 Firewall requires to provide an exception for the
service or to the port)..
as you are reporting the SQL Server Service Manager does not list the
instances, I'd guess it's becouse the very first scenario
(http://support.microsoft.com/default...b;EN-US;814132)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That did the trick. Thank you!
Is there some reason that disabling the protocols is the default? It seems
to me that without that, MSDE is completely unusable. Like I said before, I
couldn't even get osql to connect. Maybe that's a different problem.
One note: the Server Network Utility says that changes will take effect
after the service is restarted, but restarting MSDE didn't make it work. I
had to reboot the computer
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hjoumFh9q50U1@.individual.net...
> hi Paul,
> Paul Pedersen wrote:
> if you can not connect from remote client, it can be depending on disabled
> network protocols (default for MSDE installation, you can bypass at
> install time providing the DISABLENETWORKPROTOCOLS=0 paramenter or later,
> at run time, using the Server Network Utility [svrnetcn.exe] to enable the
> desired/required network protocol) or, for instance, firewall/network
> problems (Windows XP sp2 Firewall requires to provide an exception for the
> service or to the port)..
> as you are reporting the SQL Server Service Manager does not list the
> instances, I'd guess it's becouse the very first scenario
> (http://support.microsoft.com/default...b;EN-US;814132)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Paul,
Paul Pedersen wrote:
> That did the trick. Thank you!
> Is there some reason that disabling the protocols is the default? It
> seems to me that without that, MSDE is completely unusable. Like I
> said before, I couldn't even get osql to connect. Maybe that's a
> different problem.
actually not... Microsoft provided a setup that should protect all "passive"
users from external malicious attacks (think of Slammer and the like) only
enabling trusted WinNT connections and disabling network protocols ...
and this is not a bad idea... always give the least required privilege
pattern :D:D

> One note: the Server Network Utility says that changes will take
> effect after the service is restarted, but restarting MSDE didn't
> make it work. I had to reboot the computer
stopping and restarding the service is usually enonght thought...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks again.
It took a fair amount of trial before I realized that to connect to a named
MSDE instance with osql, you have to preface the name with server\, even if
it's on the same machine.
Only one problem left. In testing, I uninstalled and reinstalled with
DISABLENETWORKPROTOCOLS=0. But the instance does not show up on the network.
In other words, I installed MSDE on Machine A. The Service Manager on
Machine A can see its own server, plus an instance of SQL Server on Machine
B. Machine B can see its own SQL Server instance, but cannot see the MSDE
instance on Machine A.
On Machine A, the Network Utility says the tcp/ip default port is 1059 (must
be some default, because I did not set it), and Hide Server is not checked.
Do you have suggestions about this?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hk1c1Fhii6nU1@.individual.net...
> hi Paul,
> Paul Pedersen wrote:
> actually not... Microsoft provided a setup that should protect all
> "passive" users from external malicious attacks (think of Slammer and the
> like) only enabling trusted WinNT connections and disabling network
> protocols ...
> and this is not a bad idea... always give the least required privilege
> pattern :D:D
>
> stopping and restarding the service is usually enonght thought...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Paul,
Paul Pedersen wrote:
> On Machine A, the Network Utility says the tcp/ip default port is
> 1059 (must be some default, because I did not set it), and Hide
> Server is not checked.
it's not a default... Microsoft only got 1 unique TCP/IP port for SQL Server
assigned by IANA, port TCP/IP 1433... but named instances never listen to
that port that is "reserved" for default instances... so Microsoft embedded
an additional service in order to allow dynamic ports assignemen/resolution
for named instance, and the MDAC stack had to be modified as well to
integrate this feature (MDAC 2.6 and above)
for named instance you have 2 options... dynamic (default) or fixed static
port assignement..
loosely speaking, for dynamic port, when the specified instance starts, it
always searches for an unused port (actually it always uses the very same
port, if free)... the SQL Server Resolution Service, listening on UDP 1434
port, intercepts all remote connections to the instances, asks the instance
for it's used port, and redirects all connections to that specific port,
adding some little overhead to the traffic...
again, very very loosely speaking, the client makes a query, which is
reloaded by the MDAC stack to the appropriate network protocol [we are
talking about TCP/IP], that intiates the connection over a random tcp port
greater than 1024 to destination port; the listener service is the first
hand-shaker and intercepts the foreign call, queries the instance for it's
used port and negotiates a TCP/IP port for the 2 end points...
for a static assigned port, the listener on UDP 1434 port is not required,
but you have to map a server alias on each remote client pointing to the
correct fixed assigned port.. and again, the client intiates the connection
over a random tcp port greater than 1024 to destination port (say 1059). The
Subscriber should be allowed to make inbound (ANY) connections to
{say 1059} on a random port. The Publisher should be allowed to establish
outgoing connections to the subscriber from (ANY) to {say 1059}...

> Do you have suggestions about this?
>
WinXP sp2 or personal firewall?
the Windows XP sp2 integrated Firewall closes all ports... you have to add
an exception for that in order to allow external access,
http://support.microsoft.com/default.aspx?scid=kb;[LN];841251 ,
http://support.microsoft.com/?id=841249 ,
http://support.microsoft.com/default.aspx?kbid=839980
please excuse my poor english... hope it's understandable
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
I am having the same problem, but I am using Enterprise Manager on the same
machine where the named instance is running. What can I do, please?!
TIA
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hjoumFh9q50U1@.individual.net...
> hi Paul,
> Paul Pedersen wrote:
> if you can not connect from remote client, it can be depending on disabled
> network protocols (default for MSDE installation, you can bypass at
install
> time providing the DISABLENETWORKPROTOCOLS=0 paramenter or later, at run
> time, using the Server Network Utility [svrnetcn.exe] to enable the
> desired/required network protocol) or, for instance, firewall/network
> problems (Windows XP sp2 Firewall requires to provide an exception for the
> service or to the port)..
> as you are reporting the SQL Server Service Manager does not list the
> instances, I'd guess it's becouse the very first scenario
> (http://support.microsoft.com/default...b;EN-US;814132)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Sorry, I forgot to mention that I am using win2000p for dev and win2000
server for production
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hjoumFh9q50U1@.individual.net...
> hi Paul,
> Paul Pedersen wrote:
> if you can not connect from remote client, it can be depending on disabled
> network protocols (default for MSDE installation, you can bypass at
install
> time providing the DISABLENETWORKPROTOCOLS=0 paramenter or later, at run
> time, using the Server Network Utility [svrnetcn.exe] to enable the
> desired/required network protocol) or, for instance, firewall/network
> problems (Windows XP sp2 Firewall requires to provide an exception for the
> service or to the port)..
> as you are reporting the SQL Server Service Manager does not list the
> instances, I'd guess it's becouse the very first scenario
> (http://support.microsoft.com/default...b;EN-US;814132)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
dl wrote:
> Hi Andrea
> I am having the same problem, but I am using Enterprise Manager on
> the same machine where the named instance is running. What can I do,
> please?!
> TIA
what kind of exception are you reported with?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you for your help. Your English is fine.
I don't know why, but it started working. I didn't change anything. But if
it's working, I won't complain.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3hldmoFhkqsrU1@.individual.net...
> hi Paul,
> Paul Pedersen wrote:
> it's not a default... Microsoft only got 1 unique TCP/IP port for SQL
> Server assigned by IANA, port TCP/IP 1433... but named instances never
> listen to that port that is "reserved" for default instances... so
> Microsoft embedded an additional service in order to allow dynamic ports
> assignemen/resolution for named instance, and the MDAC stack had to be
> modified as well to integrate this feature (MDAC 2.6 and above)
> for named instance you have 2 options... dynamic (default) or fixed static
> port assignement..
> loosely speaking, for dynamic port, when the specified instance starts, it
> always searches for an unused port (actually it always uses the very same
> port, if free)... the SQL Server Resolution Service, listening on UDP 1434
> port, intercepts all remote connections to the instances, asks the
> instance for it's used port, and redirects all connections to that
> specific port, adding some little overhead to the traffic...
> again, very very loosely speaking, the client makes a query, which is
> reloaded by the MDAC stack to the appropriate network protocol [we are
> talking about TCP/IP], that intiates the connection over a random tcp port
> greater than 1024 to destination port; the listener service is the first
> hand-shaker and intercepts the foreign call, queries the instance for it's
> used port and negotiates a TCP/IP port for the 2 end points...
> for a static assigned port, the listener on UDP 1434 port is not required,
> but you have to map a server alias on each remote client pointing to the
> correct fixed assigned port.. and again, the client intiates the
> connection over a random tcp port greater than 1024 to destination port
> (say 1059). The Subscriber should be allowed to make inbound (ANY)
> connections to
> {say 1059} on a random port. The Publisher should be allowed to establish
> outgoing connections to the subscriber from (ANY) to {say 1059}...
>
> WinXP sp2 or personal firewall?
> the Windows XP sp2 integrated Firewall closes all ports... you have to add
> an exception for that in order to allow external access,
> http://support.microsoft.com/default.aspx?scid=kb;[LN];841251 ,
> http://support.microsoft.com/?id=841249 ,
> http://support.microsoft.com/default.aspx?kbid=839980
> please excuse my poor english... hope it's understandable
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>