| Author |
Topic |
|
brianatajpi
Starting Member
12 Posts |
 Posted - 2004-06-08 : 13:05:08
|
| My ISP has just blocked port 1433 (blaming virus traffic), so I can't use SQL Serevr Enterprise Manager, MS-Access, Visual Studio or DreamWeaverMX on my local machine to communicate wih SQL on my server across the Internet.Question 1I can get a port opened on my host's firewall, and I can open a matching port at my end, but can I configure SQL Server on my server to listen on a port other than 1433? How?Just in case it's significant we're talking about SQL7.0 on Win2000.Question 2Assuming I can change the server, how do I configure my client (SQL Server Enterprise Manager 8.0 on Win XP Pro) to use the new port? How?Question 3My colleagues also connect over the Internet but their ISPs haven't blocked port 1433, so if I change it they'll lose connectivity and they'll have to change too. Can I get the server to listen on *both* the new port and the old 1433 at the same time? How?Brian Lowe---------@ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-08 : 13:09:08
|
| To change the server, go to Start/Programs/Microsoft SQL Server/Server Network Utility. Select your instance, highlight TCP/IP, click properties. Then change the port. You'll need to restart SQL Server for the changes to take affect. Then on the client machines, go to Start/Programs/Microsoft SQL Server/Client Network Utility. Go to alias tab. Click Add. Type in a server alias (can be anything really, but most people just use the server name with instance name if one exists). Select TCP/IP. Type in the server name or IP address in the server name field. Uncheck the dynamic port option. Type in the port number that you specified in Server Network Utility. And no you can't get the server to listen on two ports. So they must do the Client Network Utility thing too. BTW, 1433 isn't recommended in an internet environment anyway, so your ISP did the right thing.Tara |
 |
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-08 : 13:21:29
|
| Thanks for the quick response!Maybe the ISP did the right thing, but they didn't tell anyone they were blocking the port - not even their own front help desk, so it has taken me most of today, arguing with various tech support people, to determine just where the blockage is.I feel they should have warned me in advance that I was going to lose it and maybe offered some advice on how to set up alternate routes. Even if it was after the event I'd have appreciated a broadcast email saying "We've blocked the following ports because of virus traffic: 1433, ... Here's a link to help you reconfigure to avoid/reduce loss of service."Thanks for your help with this.Brian Lowe---------@ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-08 : 13:23:26
|
| Yes they definitely should have notified people.Tara |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-09 : 06:07:12
|
| OK. This is probably a good solution to the problem I presented, but a side effect is that after I change the port in the network utility IIS (running on the same machine) loses access to the SQL databases.I'm really in a stuck place. If I set it so that my web sites work OK I can't develop anything because all my tools need to work across the Internet and the port is blocked. If I set it so that I can work on stuff all my data-enabed web sites go down.Tell me about "instances". Can I have 2 instances running using the same data? with one on the default settings so IIS can see it and another on the new port so I can manage data remotely?Brian Lowe---------@ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 12:20:30
|
| No, that is not possible. Instances contain different databases. I don't understand your problem though. Why does the network utility IIS lose access to the SQL databases.Tara |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-09 : 12:36:12
|
I don't understand it either!I'm a developer trying to build an application that just happens to be web based. I've had to expand my skill set to encompass Windows server, IIS, FTP, SQL, FireWalls and more. I'm spreading myself pretty thin trying to be a jack of all trades and ending up master of none.I really know that a full-time DBA would have his SQL server set up on a non-default port to begin with, and a full-time webmaster would have a strong enough grip on IIS to manage connectivity. I just wish I had enough time to learn it all.  I'm very thankful for forums (fora?) such as this where there are experts willing to help out.Brian Lowe---------@ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 12:40:03
|
| IIS should be able to see the server even if SQL is not listening on 1433. I would suspect that your client is referring to 1433 in the connection string. Check your connection string. Only reference an alias or the server name in it. Then have a corresponding alias setup in Client Network Utility.Tara |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-09 : 12:56:04
|
Persist Security Info=False;Data Source=195.224.88.156;Initial Catalog=RoarAlmanac;User ID=secret;Password=secret;Now there may be something. My connection string has the IP address of the server rather than the server name or instance name (set up that way so I could set the connection string on my workstation and not have to change it when I upload my code).Obviously IIS is going to send the request over TCP/IP (not knowing that it would end up back on the same machine). IIS would use the default port because I haven't set anything up in the client side on my server, but SQL was listening on the non-standard port.Sheesh!I feel like I'm getting there! Brian Lowe---------@ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 12:59:34
|
| So change your connection string to use the server name. Then setup an alias in Client Network Utility for that server and the port that it is listening on. Then you should be good.Tara |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-09 : 13:09:03
|
| ThanksI'd decided that was what I should do, but I figure I need to order my events;1Set up the alias in the Client Network Utility on the machine that runs IIS (same as SQL) to use the non-default port.2..n-2Change connection strings in all web applications to use the alias instead of the IP address of the SQL server.n-1Change the port that SQL server listens on in the Server Network Utility on the machine that runs SQL (same as IIS)nSet up the alias in the Client Network Utility on the machine that I use for development to use the non-default port.Brian Lowe---------@ |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-10 : 06:00:58
|
| I was wrong.Step #1 should be to set up the alias on the IIS machine to use Multiprotocol and a name. That way it will bypass the TCP/IP altogether.When that's done I'll change all the connection strings in all my web sites to use the alias, then when I switch the TCP/IP port none of the local/IIS stuff will be affected.Brian Lowe---------@ |
|
|
|
brianatajpi
Starting Member
12 Posts |
Posted - 2004-06-10 : 13:56:41
|
| Done!Got through changing all my connection strings a while ago (testing each site was a pain - I didn't dare use a global search/replace in case one site out of 70 got screwed Set up my alias locally and now my local SQL client, Access and Visual Studio all work like a charm )Only problem now is DreamWeaver MX - I'm off to find another forum.Thanks for all the help.Brian Lowe---------@ |
|
|
|
|
Can I select a port for SQL ?