Thursday, February 3, 2011

SQL Server 2008 connection

Hi all,

I'm trying to connect to my SQL Server on my VPS but i can't get this to work.

  • I have enabled the TCP/IP protocol on SQL.
  • I also have the SQL Server and SQL Browser service running as a network service.
  • I turned off my firewall on my server and on the client (I won't keep it like this it's just for testing).

After doing all this it still doesn't work.

It seems that i can't reach port 1433 or 1434 either:

C:\>telnet myserver 1433
Connecting To mrsoundless.com...Could not open connection to the host, on port 1
434: Connect failed

C:\>telnet myserver 1434
Connecting To mrsoundless.com...Could not open connection to the host, on port 1
433: Connect failed

Could someone explain why this could be happening?

  • Note1: I'm new to MSSQL.
  • Note2: The server is a VPS for personal use.
  • Note3: I use SSMS to try to connect to SQL Server 2008 Express
From serverfault MrSoundless

  • After you change connection settings, such as enabling TCP/IP and listening on a static port, you need to restart the MSSQL service for that instance for those changes to take effect.

    MrSoundless : I did. I even restarted the server to make sure the right service was restarted!
    From MarkM

  • Is it listening on port 1433? Try running from the command prompt:
    netstat -ano
    If it doesn't say TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING in there it's not listening. If it does, try connecting to localhost instead of myserver - if that works, you sir have yourself a firewall issue. Check Windows Firewall then phone your provider.

    MrSoundless : I used portqry to check if the ports are listening. It seems that 1434 is listening. When I run portqry from the client it says it's filtered. 1433 is not listening at all which I think is weird because the port is set to 1433 by default in the SQL Configuration Manager. Also, as stated in the main post, both firewalls on server + client are turned off (unless you're talking about hardware firewalls, not sure about that...)
    From James Lawrie

  • By default, Express allows only Windows Authentication mode, so you should "enable remote connections":

    Also, telnet service should run on your VPS server for quering with telnet?

    BTW, where from are you telnetting?

    Related question:

    MrSoundless : I get the 26 error shown on that webpage. I have already done everything shown there except that my SQL browser is running as a network service. So just changed that to run as a local service. I still have the same problem. Where from? If you mean geologically: From Netherlands to Netherlands.
    vgv8 : No, I mean geographically. Is it from computer from the same AD? I thought that VPS are rented remotely.
    vgv8 : What do you mean under "I get the 26 error shown on that webpage"?
    vgv8 : Yoг cannot serve MS SQL Server remotely from "Networking Service". It is only for local development on the same machine. Elevate account under which server runs. (Note that SqlExpress is not supposed to be accessed remotely, it is to be used, for ex., by webserver on the same machine)
    MrSoundless : the VPS is rented remotely but it is also located in the netherlands
    MrSoundless : I mean I get this error when trying to connect to my VPS with SMSS on the client: Cannot connect to ip\SQL2008. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Microsoft SQL Server)
    MarkM : *Also, telnet service should run on your VPS server for quering with telnet?* - What MrSoundless is doing is commonly referred to as banner grabbing. He is telnetting to an alternate port that should be open to see if he can create a connection to it. In this case 1433.
    vgv8 : So, you have non-default (not .\SQLExpress but .\SQL2008) named instance????
    MrSoundless : I named it SQL2008, also the reason I would like remote access is because I want to be able to do fast testing.
    vgv8 : try to connect, then, with "telnet myserver\SQL2008 1433" because, I believe "telnet myserver 1433" is for default instance (\SQLExpress). Why cannot you test from the same machine. SQLExpress usually is not intended to be exposed remotely
    vgv8 : Is your SSMS client in the same AD domain?
    MrSoundless : It's not in the same domain I guess. Also telnetting like that doesn't work either. I got a php/mysql background where remote db access is pretty normal for easyness of testing. I wanted to carry on that thought to MSSQL
    vgv8 : On the myserver, In SQL Server Configuration Manager make sure that TCP/IP is enabled both in "Protocols for SQLExpress" and in "Client Protocols" + in SSMS, right-click on server name --> Properties --> Connections ----> check "Allow remote connections to this server"
    vgv8 : You should connect in SSMS as "Server name:" myserver\SQL2008
    vgv8 : You must restart SQL Server service for changes to take effect
    MarkM : @vgv8 telnet doesn't care about a SQL Instance name. If you bind an instance to a port, you only need *telnet servername portNumber*.
    MrSoundless : I had those all enabled already + I do connect by using mysql\SQL2008
    vgv8 : In SSMS connect through "SQL Server Authentication" instead of "Windows Authentication", for ex., through sa user
    vgv8 : What is your connection string?
    vgv8 : http://www.connectionstrings.com/sql-server-2008#p3
    MrSoundless : I do use SSA and I have no idea what my connection string is. If I did know, I wouldn't give it anyway... Anyway I'm off to bed. I'll try to contact my host.
    vgv8 : @MarkM, we were discussing connection through SSMS, not through telnet
    MarkM : @vgv8 - Then why did you say *"try to connect, then, with "telnet myserver\SQL2008 1433" because, I believe "telnet myserver 1433" is for default instance (\SQLExpress)."*?
    squillman : I just read through this conversation chain. It's somewhat disturbing. Very little of what you are saying makes sense or is even applicable to the problem. 1) Windows authentication mode and remote connections have nothing to do with each other. 2) telneting into a host does not require a telnet service on the remote host. It requires an open socket, that is all. 3) there is no connection string at play here. He's just trying to get SSMS to connect, not code.
    squillman : Based on this and other answers you've submitted it is clear that you have a lot to learn about system administration. If you don't know, please don't pretend you do. Please don't answer and lead people down wild goose chases.
    vgv8 : @squillman, topic starter wrote: 1) "I got a php/mysql background where remote db access is pretty normal for easyness of testing. I wanted to carry on that thought to MSSQL"
    vgv8 : @squillman, 2) "I had those all enabled already + I do connect by using mysql\SQL2008",
    vgv8 : @squillman, so I understood that topic starter was connecting from MySql or Php script/code. How, can you use SSMS from MySql?
    squillman : @vgv8 You clearly misread his post. "My SQL" != "mySQL". He's trying to connect to SQL Server, not mySQL. Look at the tag. There clearly is no code involved. The OP clearly states in point #3 of his question (the very last line) that he's using SSMS.
    MarkM : @squillman - better to let the troll keep on trolling until he accumulates another boxing. I think the OP has picked up that he has no idea what he's talking about.
    squillman : @Mark yep, agreed. I'm done here.
    From vgv8

  • Sounds to me like your VPS host has a firewall or other filtering device sitting in between you and your server. Give them a call and see if this is the case and if so see if they can change the configuration to allow connections through to your VPS.

    MrSoundless : I will try doing that. Thanks
    From squillman

  • I am vgv8, I was banned. This is fast answer since I am actively being blocked from site

    SqlExpress, by default has TCP/IP protocol disabled (it is intended to be used locally) and should be enabled.

    You cannot connect using "Windows Authentication" from untrusted non-domained computer , i.e. from outside.

    Also, check that "Use Simple File Sharing" on server, otherwise you can connect only by Guest account (disabled by default in Windowses).

    To connect to named (non-default) instance from SSMS you should enter in SSMS yourSrtvrName\SQL2008, or, if you do not have DNS resolution ip-address-of-sql-server\SQL2008

    From pizdets
Posted by Mu Cat at 8:51 AM

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)