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!
LVL 7
ottobockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.