Problem with DTS connection using an DNS alias

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Problem with DTS connection using an DNS alias

Author

Topic

dayafxela
Starting Member

2 Posts

Posted - 2009-05-29 : 04:07:56

Hi,

I have a problem to create a DTS connection and an ODBC DSN by using an alias DNS in a SQL server 2005

Here below some explanations :

I need to migrate Databases, DTS packages from SQL server 2000 to a SQL server 2005.

In order to not use the server name in all applications and future migration I have had to set up an alias DNS (SQLAPP) to log on the SQL server.

This DNS alias is linked directly to a SQL server named instance ([servername]\APP). We have set up the SQL instance through the "SQL server configuration manager" :

[servername]\APP (named instance) = [IP address] = SQLAPP (the alias DNS)

When I log on the SQLAPP (SQL server management studio 2005), it works perfectly.

Nevertheless, I have a problem while creating a DTS package connection or an ODBC connection by using the DNS alias - it doesn't work.

The error message is:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (invalid instance()).] Invalide connection.

For information the Named Pipe is up and on the new SQL server 2005 when I execute :
select @@servername
go
sp_helpserver
Here are the results :
SQLAPP
**********
name network_name status id collation_name connect_timeout query_timeout
SQLAPP SQLAPP rpc,rpc out,use remote collation 0 NULL 0 0

Is there any parameter to set up that I haven't done?

I would really appreciate if someone could help me

Regards,

mike--mike
Starting Member

1 Post

Posted - 2010-01-18 : 08:31:23

Hello! Did you figure this one out? I have the same issue...

Cheers, Mike

dayafxela
Starting Member

2 Posts

Posted - 2010-01-18 : 09:51:30

You need to add the "TCP Port" after the name of your alias DNS with the following syntaxe :

[ALIAS NAME],[TCP PORT] in the Server zone of your ODBC

The solution above is for a "SQL Server" (SQL Server 2000) Drivers
If you choose a "SQL Native Client 10.0" (SQL Server 2005) Drivers you needn't to add the TCP Port

Regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources