May 29, 2003 at 5:38 pm
Hi all,
I have SQL Server 2000 Personal Edition installed on a box in my home. I am not able to use Enterprise Manager to connect to a remote SQL Server 2000. I get the error "SQL Server does not exist or access denied. Connection Open(Connect())". When I try with Query Analyzer, I get "Server: Msg 17, Level 16, State 1".
There are tons of other forumn messages out there but none have seemed to solve my problem. I recently discovered that the cable modem company I'm using blocks traffic on port 1433. Key issue. I don't know how to set EM to use a different port number to get around this issue? Can anyone help me out on this? Right now I can't connect to administer the remote servers and it's killing me!
May 29, 2003 at 5:44 pm
SQL Server errorlogs have TCP port number used by SQL Server in folder \mssql\log
May 29, 2003 at 7:36 pm
Hi Allen,
Thanks for taking the time to reply. I certainly appreciate it.
However, I still don't understand what that does for me? I did as you said and it just confirms to me that it's listening on Port 1433. What I need, is to change which the port my local server is listening on to something other than 1433. I'm not sure on how to accomplish this? I'm sure the remote server is listening on 1433 but I can't have traffic on that port so I need a solution to go around that restriction.
quote:
SQL Server errorlogs have TCP port number used by SQL Server in folder \mssql\log
May 30, 2003 at 4:39 am
Are you saying you need to change the port your SQL Server is running on?
If so then open
Server Network Utility
you will find the configuration is best handled under there. Click the TCP/IP connection an choose properties, there is where the port can be configured. Once done you will need to restart the SQL Server service for the new port to take affect.
However if you need to connect to the remote machine you will still be blocked if block both incoming and outgoing. I am assuming they did this in response to Slammer. I suggest contacting them and having them reopen port 1433 as slammer cannot affect the machine there just on 1434. I would suggest using the statement you are a customer and paying for complete service which you are not getting (this is usually enough) and if they are that stupid find another carrier is best or tell them to discount your service by half since this is something you need and they choose to block your needs (by the way who is it ?).
May 30, 2003 at 2:14 pm
Thanks for your post!
I am saying I need to change the port my SQL Server is running on. I'm hoping that will solve the problem. I use COX and they block that port (and a few others):
I was hoping I could get SQL Server to use another port and get around this whole mess?
quote:
Are you saying you need to change the port your SQL Server is running on?If so then open
Server Network Utility
you will find the configuration is best handled under there. Click the TCP/IP connection an choose properties, there is where the port can be configured. Once done you will need to restart the SQL Server service for the new port to take affect.
However if you need to connect to the remote machine you will still be blocked if block both incoming and outgoing. I am assuming they did this in response to Slammer. I suggest contacting them and having them reopen port 1433 as slammer cannot affect the machine there just on 1434. I would suggest using the statement you are a customer and paying for complete service which you are not getting (this is usually enough) and if they are that stupid find another carrier is best or tell them to discount your service by half since this is something you need and they choose to block your needs (by the way who is it ?).
May 30, 2003 at 2:29 pm
Go the other port route. One of the reasons to block on 1433 has to do with the SQL Spida worm (SQL Snake, SQL Sapphire, etc.) and the one that came out before that. They make a connection on 1433 and try to login as sa with no password. Hence, if you block 1433, those worms no longer propagate.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 2, 2003 at 7:53 am
I don't know about other readers, but I'm kinda confused.
I get these two:
1. You have SQL Server Personnal Edition on your home computer.
2. You have Enterprise Manager on your home computer.
But what I'm confused about is what are you trying to connect to?
a. YOUR SQL Server?
or
b. a REMOTE SQL Server?
If it's a, then you shouldn't be having any problems; it's all on the same computer.
If it's b, then YOUR SQL Server has nothing to do with it. Only Enterprise Manager counts at this point. In this case:
do you have permission to access the remote SQL Server?
which port is the remote SQL Server instance listening on?
If it's listening on 1433, you need to talk to whomever is in charge of the firewall, they need to permit your machine to have access. Port 1433 can't be blocked if they are allowing remote access, but they can block IP addresses from accessing Port 1433. If Port 1433 is really being blocked, then the remote location is not allowing ANY remote access.
-SQLBill
June 2, 2003 at 2:57 pm
SQLBill,
Thanks for your note. The answer to your question is B. I'm trying to use Enterprise Manager to connect to a remote SQL Server. I am allowed to connect, via port 1433, but that isn't the issue. I have an internet access provider, COX,that is blocking traffic on port 1433 so I can't even get to the remote host. In the case where I could, I do have authentication and authrization information to use.
I learned since then that they have this port shut down for residential access but have it opened for business access. I'd have to switch my accounts in order to get through. Of course, it's a lot more expense and requires a 1 year contract. 😉
Hope that cleared it up.
quote:
I don't know about other readers, but I'm kinda confused.I get these two:
1. You have SQL Server Personnal Edition on your home computer.
2. You have Enterprise Manager on your home computer.
But what I'm confused about is what are you trying to connect to?
a. YOUR SQL Server?
or
b. a REMOTE SQL Server?
If it's a, then you shouldn't be having any problems; it's all on the same computer.
If it's b, then YOUR SQL Server has nothing to do with it. Only Enterprise Manager counts at this point. In this case:
do you have permission to access the remote SQL Server?
which port is the remote SQL Server instance listening on?
If it's listening on 1433, you need to talk to whomever is in charge of the firewall, they need to permit your machine to have access. Port 1433 can't be blocked if they are allowing remote access, but they can block IP addresses from accessing Port 1433. If Port 1433 is really being blocked, then the remote location is not allowing ANY remote access.
-SQLBill
June 2, 2003 at 5:05 pm
I would press the fact that you pay for complete service and they are not providing such. But check your contract from when you setup, if it fails to mention they block ports you may actually be able to force them on the issue. If they did not make a statement on blocking within your agreement then they must provide complete and total service to you. However, it is somewhat of a pain to push if they really push back. Just speak with a manager instead of a peon and let them know you are unhappy with such, they should be able to unblock you at request.
June 3, 2003 at 6:29 am
The port change has to be made on the SQL Server itself not on the client side. Then the clients have to set up the ODBC connection to that port. So, the port change can't be made JUST for you, it will affect EVERYONE using that SQL Server. You need to get the owners of the remote SQL Server to:
1. Change the listening port and force EVERYONE to change to that port (unlikely).
2. Direct the firewall owners to add your IP/Machine address to the access list for port 1433.
-SQLBill
June 3, 2003 at 6:40 am
By the way, it appears you may be mis-understanding how ports work. Let me provide an analogy (if you do understand ports, please forgive me).
I (server) have a phone number (port) 555-1111, I also have caller ID (firewall) which allows me to block/allow selected callers. I block everyone but my family and friends. That means you can call 555-1111 all day long and never get through. You can try different numbers 555-2222, but I'm not LISTENING on those numbers so it doesn't work. The only things that will work are:
I change my phone number to one you like, but then all my friends and family have to change the number they call or I change my caller ID to allow you access.
Basically, that's how this is all working.
-SQLBill
June 3, 2003 at 6:00 pm
SQLBill,
Thanks for your time and analogies. My understanding was furthered by it.
After I found out it was COX, I posted to a local SQL Server user group hoping that someone else had this problem already and managed to take care of it. Mostly everyone was in disgust with COX and this issue had come up. The only way around it is Qwest or a business account with COX.
As per your analogy, I'm assuming COX is the "CallerID" and is thus blocking my IP from the "access list".
thanks,
quote:
By the way, it appears you may be mis-understanding how ports work. Let me provide an analogy (if you do understand ports, please forgive me).I (server) have a phone number (port) 555-1111, I also have caller ID (firewall) which allows me to block/allow selected callers. I block everyone but my family and friends. That means you can call 555-1111 all day long and never get through. You can try different numbers 555-2222, but I'm not LISTENING on those numbers so it doesn't work. The only things that will work are:
I change my phone number to one you like, but then all my friends and family have to change the number they call or I change my caller ID to allow you access.
Basically, that's how this is all working.
-SQLBill
June 4, 2003 at 5:32 am
Yes, COX is acting as a CALLER ID and is not allowing you access.
If the business that owns the SQL Server requires you to be able to access it from home, then they should pay COX or require them to allow you access. As I'm not you, I don't know whether this is for your convienence or a business requirement; only you and the end business knows that.
Best of luck...
-SQLBill
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply