Instance alias

[djj55]

Hello, SQL 2008R2
I am trying to alias a SQL server instance and have tried two ways.
1) SQL Server Configuration Manager
2) SQL Server Client Network Utility (Cliconfg.exe)
However, neither worked as I set them up.

I checked to see what the default TCP/IP port in SQL Server Configuration Manager of the linked instance.

When I try to connect the error is:[tt]
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)[/tt]

Notice it talks about named pipes but I set up TCP/IP.

Any suggestions?
Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Why are you trying to alias a SQL Server Instance? Specifically, I'm curious as to what advantage this gives you.

When I try to connect the error is:

What are you trying to connect with? Specifically, you must be using some software to attempt the connection.

When troubleshooting connection problems, I often use the "UDL" trick. Here's how it works.

1. Create a shortcut on the desktop. I usually name it udl.udl
2. double click the short cut. You should see a window named "Data Link Properties".
3. Click the provider tab, and select your preferred provider.
4. Click the Connection tab and select the server and database.
5. Click the "Test Connection" button.
6. click OK
7. Open the udl.udl file in a text editor (notepad). You will see the connection string that the Data Link Properties used to connect to the server.

You can use settings in the connection string to force it to use TCP/IP. For example:

Taken from connectionstrings.com
[tt]
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;[/tt]

There are a couple of interesting things about this connection string. First, notice the data source: Data Source=190.190.200.100,1433; This means we are connecting to an IP address of 190.190.200.100 and a TCP port of 1433.

Next, notice the network library part: Network Library=DBMSSOCN This forces the connection to use TCP/IP.

Make sense?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

I am using SSMS. I would like to use mySQL instead of [myServer3\mySQL3] note this is a test as I will use it for an IP address instance (190.190.200.100\mySQL3). It means I do not have to remember the IP when writing my t-sql.

Working on your suggestion but am having trouble creating the shortcut as I get the wizard which wants a "program".

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Hello,
Finally figured out that I should use "Other data source" even though I was using a SQL server. This let me name the data source and give the linked server the name I needed.

Thanks


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

For individual use I found How to setup and use a SQL Server alias which sets an alias on your instance by using SQL Server Configuration Manager.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!