?
Solved

Connection issue to sql 2005 behind firewall.... TCP vs. Named pipes

Posted on 2007-08-09
9
Medium Priority
?
1,942 Views
Last Modified: 2008-01-09
Hello,
I know this topic has been covered pretty heavily, but i've not come accross a solution that is specific enough to our issue.  We are moving to SQL 2005 for a web app. We have Firewalls between the IIS 6 Web Server and the Sql Server 2005 (64bit if that makes a difference).

If we try to close down the ports between the servers then the application that talks to the sql server gets the access is denied or Server does not exist error. If i open it up to IP Any it works fine.
TCP any with UDP any doesn't work either. I've tried running ethereal on it but i can't see any port connections besides teh 1433 and 1434.

On the SQL Server both TCP and named pipes are allowed for remote connections. but how can i tell if it's using TCP? And shouldn't that work with TCP 1433 and UDP 1434?

I know we are using a named instance on the server as we have to change the connection string to ip\instance_name but does that mean we are using named pipes?

Thanks!
0
Comment
Question by:tonkajeep34
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 19667260
You would use TCP over a firewall and not named pipes.  Here is a KB article about TCP and firewalls for SQL Sverer.

Ben.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19668141
The issue is in the named instance !!
I've been breaking my head over the same issue. I could not find a real sollution, so I stopped when I had this workable workaround ... but it's not the ideal situation !

Create on the connecting server an alias for DBMSserver\instance thant looks like DBMSserver_instance (omit the '\' character !) ... it worked for me ... but don't ask me why.

Big disadvantage is that you have to create an alias on every connecting machine that has this problem. Big advantage is that you only have to change something on machines that have an issue.

Good luck !
0
 
LVL 1

Author Comment

by:tonkajeep34
ID: 19673187
dbaduck, Could you post that link again?

Yes i want to use TCP but i'm not sure if it is or not and how to tell. Obviously the issue is that when i close down the firewall to what i've read TCP uses it doesn't work so i'm assuming that it is using named pipes instead of tcp. but i don't know what will happen if i just go and change the surface config to be tcp only.

Thanks
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:tonkajeep34
ID: 19673213
Yveau,

What type of alias did you create? was it an odbc data source or like an alias in the host file? I'd like to give it a try just not sure which one to try yet.

Thanks
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19673610
Sorry, should have been more specific. A SQL alias. Use the SQL Server configuration manager and then under the 'SQL Native Client Configuration' you can create an alias.

Good luck.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19844529
tonkajeep34,

Is your Q answered or can I be any more assistance ?
0
 
LVL 1

Author Comment

by:tonkajeep34
ID: 19848064
Sorry i forgot to reply. Our DBA tried that and it still didn't work. I think he's decided it's time to call M$ support.

Thanks,
Mike
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1000 total points
ID: 19848162
Here is the KB article that I talked about.
http://support.microsoft.com/kb/287932

Not sure what I was thinking when I hit submit last time.
Ben.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19849237
If we can't help you any more, can you please close the Q ?
Thanks !
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question