• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

SQL Server Uses Named Instance w/ Dynamic Ports - Client App Cannot Connect

Hello Experts.

I have a clustered SQL 2005 Server using named instances and dynamic ports 1433 and 1815. There is a client application which needs to connect to a SQL db, but struggles with the named instance required.

I.e. configuration string is SQL_SERVER \ SQL_Instance : 1433

This application has a problem, because if I put in SQL_SERVER \ SQL_Instance, it does not even try to connect via 1433. However, if I put in SQL_Server, I can see (via netstat), it tries to connect using 1433, but fails because the instance is missing. There is nothing in the app to adjust port.

I'd rather not make any changes to the server, as it's clustered, and other apps/clients currently connect without issue.

Anything I can do on the client to fool the app maybe?  Perhaps an SQL agent which connects to the server properly, and the app then connects locally to the agent? Other ideas?

Last note, this client app actually is on a DMZ, so there is some port restrictions, NAT, and advanced network configuration to make for secure communication.
Thanks!
0
ottobock
Asked:
ottobock
  • 4
  • 3
2 Solutions
 
Chris MConsulting - Technology ServicesCommented:
You're definitely going to have issues working with the DMZ since you're working with a cluster with dynamic ports.

I can imagine the issues associated with connecting from the DMZ to your cluster through the firewalls.
Look out for how to configure static ports for SQL 2005 cluster otherwise your apps may fail in case the ports change.
Here's how:
http://support.microsoft.com/kb/823938


You may alternatively configure your instance to listen on multiple static ports too if you think you will achieve connectivity to multiple of them.
Here's how:
http://support.microsoft.com/default.aspx/kb/294453?p=1

Good luck.
0
 
ottobockAuthor Commented:
The main problems are that the app doesnt like named instances, and that I cannot set a static port in the application software (through they support DMZ configurations, as it is a web-based app).

I tested an SQL connection via ODBC, setting a static client port, and the conenction is freakin flawless. :-)

And as should, the app defaults to a static 1433 "IF" no named instance is used...

There must be a way I can trick this app so is can connect... The SQL server doesnt have a default instance - if I could create an alias or something and use 1433, maybe this could work?? Any thoughts?
0
 
ottobockAuthor Commented:
According to this site: http://www.mssqltips.com/tip.asp?tip=1620
- "Some application do not give you the ability to connect to a named instance, so this is another way of connecting without having to use the a default instance"

So I setup an alias: SQLSVR2 on port 1433 and pointed it to SQLSVR\Instance. This actually worked perfect when testing on the SQL server. I opened SQL Mgmt Studio, put in SQLSRV2, and it connected to the correctly to SQLSVR\instance!

So I got excited and went to the DMZ machine, but then wondered how it makes the connection, as the hostname cannot be resolved. Well, it seems if I use only the IP address of the cluster AND ensure the application is conencting to 1433 (which is will if an instance is not made) - than VIOLA!

I hate making changes on my SQL server though - it is productive and I may be making other problems. Any thoughts on this change? Any problems I could have made for myself???
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Chris MConsulting - Technology ServicesCommented:
If you application does not accept named instances, then go to your application server and create an alias using either "SQL server client network Untility" for SQL 2000 or go to SQL 2005's "configuration manager" where you can creater an alias and map it to a named instance.
This way, you can let your application connect via an alias that you created and then the alias will map to a mapped instance 9static or dynamic ports - no problem).
This will definitely be a smart way of doing things.
0
 
Chris MConsulting - Technology ServicesCommented:

If you application does not accept named instances, then go to your application server and create an alias using either "SQL server client network Untility" for SQL 2000 or go to SQL 2005's "configuration manager" where you can creater an alias and map it to a named instance.
This way, you can let your application connect via an alias that you created and then the alias will map to a mapped instance (static or dynamic ports - no problem).
This will definitely be a smart way of doing things.
In this case, you do not need to make changes to your SQL server then.
0
 
Chris MConsulting - Technology ServicesCommented:
Make these changes (create the alias) on the machine which runs the application, no the database server.
0
 
ottobockAuthor Commented:
Problem solved - thanks for helping me get there!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now