none
Unable to make a remote connection to sql server 2008 R2 over a work network

    Question

  • I have a windows 7, 64 bit client computer connected via a work type network to a windows 7, 64 bit server running sql server 2008 R2. I am trying to connect from a .net c# program on the client to the server using the sqlconnection object. The connection string is: "Data Source=ROOM-3\\SQLEXPRESS;Initial Catalog=AtsmDatabase;Integrated Security=True". The open method generates a 18452 error. logon failed for user ''. The user is not associated with a trusted sql server connection.

    Sql server 2005 was running on the server. I did an upgrade to 2008 R2. Under Start \ Programs there are now 2 folders one for 2005 and one for 2008. Under 2005 is Configuration Tools with sql server configuration manager, sql server error and usage reporting and sql server suface area reporting. Under 2008 R2 is also Configuration manager with sql server installation center 64 bit.

    questions:
    1. why is sql server 2005 still present on the server even after the upgrade to 2008 R2?
    2. Why do the service for sqlserver and sqlbrowser point to program files x86 even after I installed the 64 bit upgrade?
    3. why can't I connect to sql server on the server?

    Thursday, February 02, 2012 6:34 PM

Answers

  • If port 1433 is used on the server, the error is "No connection could be made because the target machine actively refused it."

    This means that the firewall is blocking your connection attempts. The timeout errors also referred to could also be caused by improper firewall configuration. The next step would be to completely disable the firewall software, restart the sql server services including browser, and then attempt to connect again.

    -Sean

    Monday, February 13, 2012 5:35 PM

All replies

  • Hello,

     

    1. why is sql server 2005 still present on the server even after the upgrade to 2008 R2?

    If you did an in-place upgrade (which is probably what happened) 2008R2 was installed over top of the older instance, but it doesn't uninstall it. If you did a side-by-side upgrade once again the older insatnce is not uninstalled. We'd need more information.


    2. Why do the service for sqlserver and sqlbrowser point to program files x86 even after I installed the 64 bit upgrade?

    Probably because it was an in-place upgrade of a 32-bit 2005 install. Check the dates and version of the actual binaries.

    3. why can't I connect to sql server on the server?

    I don't believe (I'm not positive though) that configuration settings are pulled over with an in-place version upgrade (as opposed to an edition upgrade which doesn't affect this). I re-read the 1st paragraph and you are actually connecting, but being denied. Check the sql server errorlog for the state of the 18452 error which will tell you why, but my guess is that the login doesn't exist, or the instance is having issues communicating with a DC - but the errorlog will tell you, I'm just guessing.

    -Sean

     


    Thursday, February 02, 2012 7:40 PM
  • I checked the binaries and they were still version 2005. Then I tried to do the upgrade again and it terminated with an error that no features can be upgraded for this release. Therefore I uninstalled everything sql server and did a new install of sql server 2008 R2 express edition.  I selected windows authentication during the install. Now I cannot connect from the server. It terminates with error of invalid password. From the client the error is: server was not found or not accessible. I verified that the sql brower and sql express are enabled in the firewall and the browser and sql express services are running. What is the problem now?
    Thursday, February 02, 2012 10:38 PM
  • If you chose windows authentication you will be using your windows account for credentials. There is no username or password used, your windows token is passed to SQL Server. If you are getting invalid password then you need to switch to mixed mode where you can use sql authentication (username/password combo).

    1. Go through the guide here (still applicable to 2008): http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

    2. Change the security to mixed mode: http://msdn.microsoft.com/en-us/library/ms188670.aspx

    3. Test the sql login locally (this should use either shared memory or named pipes, not tcp)

    4. Test the login remotely (this uses tcp)

    -Sean

    Thursday, February 02, 2012 11:49 PM
  • hi Sean,

    you wrote:

    >3. Test the sql login locally (this should use either shared memory or named pipes, not tcp)

    can you please expand on this?

    thank you


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
    Thursday, February 02, 2012 11:53 PM
    Moderator
  • If he uses SSMS to test the login (username/password) on the server that sql server is running (local) it should connect through shared memory or named pipes, depending which are enabled for use (which I believe both are enabled on express by default). This would show that indeed the instance is running properly and the login username and password are valid for the instance. This way if he can't connect remotely we know that at least the instance is running and the login is valid so we can take that part out of the troubleshooting equation.

    -Sean

    Friday, February 03, 2012 1:59 AM
  • I have two computers running windows 7 64 bit connected by a private work network configured to be client server with sql server 2008 R2 running on the server.

    1. why is sql server 2005 still present on the server even after the upgrade to 2008 R2?
    2. Why do the service for sqlserver and sqlbrowser point to program files x86 even after I installed the 64 bit upgrade?

    I used the upgrade option from 2005 to 2008 R2 which did nothing. The version of sqlservr.exe was 2005.90.5000.0 after the upgrade. I tried to run the upgrade again and it terminated with an error that no features could be selected for this version. Therefore I uninstalled all of sql server and installed a new version of 2008 R2. The workaround worked in that now 2008 R2 64 bit is running. The purpose of the upgrade is to avoid having to uninstall and install a new version but the upgrade doesn't work.  This part is fixed.

    3. why can't I connect to sql server on the server?
    During the 2008 R2 install, I selected Windows Authentication. I am logged in as an administrator on both the client and server. After the 2008 R2 install though I cannot connect either on the server or the client.
    The connection string is "Data Source=ROOM-3\\SQLEXPRESS;Initial Catalog=AtsmDatabase;Integrated Security=True"

    Connecting to the database from the server gives the error {"Cannot open database \"AtsmDatabase\" requested by the login. The login failed.\r\nLogin failed for user 'Room-3\\AtsmServer'."} System.SystemException {System.Data.SqlClient.SqlException}

    Connecting to the database from the client gives the error {"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"} System.SystemException {System.Data.SqlClient.SqlException}

    new questions:

    1. Why can't I connect on the server? I am logged on as adminstrator and specified windows authentication.'

    2. Why can't I connect from the client? The server firewall is configured, services are running and there is network connectivity.

    Friday, February 03, 2012 1:55 PM
  • Hello,

    I think there are a few misunderstandings. When you designate "work network" are you talking about a private network with a domain controller (AD) setup? If not, SQL Server will not be able to validate any windows credentials sent from a remote computer, only the local server that it's installed on. This is (if you aren't using AD) would be part of the reason why you can't connect, but there is more.

    >>"The connection string is "Data Source=ROOM-3\\SQLEXPRESS;Initial Catalog=AtsmDatabase;Integrated Security=True"
    >>Connecting to the database from the server gives the error {"Cannot open database \"AtsmDatabase\" requested by the login. The login failed.\r\nLogin >>failed for user 'Room-3\\AtsmServer'."} System.SystemException {System.Data.SqlClient.SqlException}"

    Your connection string is using windows authentication as denoted by Integrated Security=True. In this instance you are on the local server so it should work, and infact it actually does work as it connects you. You fail to login though because your explicitly defined initial catalog either doesn't exist in the instance or you don't have access to it inside of SQL Server.

    >>"Connecting to the database from the client gives the error {"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"} System.SystemException {System.Data.SqlClient.SqlException}"

    This is what I expected. Notice that the protocol used in the error was named pipes. This tells me that at first it "probably" tried to connect via tcp and failed so it attempted to use named pipes (you can change this behavoir). The first thing I will say is that I don't believe it is properly setup to allow remote connections to the instance form the link I gave you above. Secondly, windows authentication is being used and if you did connect to the isntance an error would have occured, either "... login from an untrusted domain..." as SQL Server couldn't verify the windows token, though if you didn't get that you most certainly would have had the "cannot open database" error that you had above.

     

    Friday, February 03, 2012 3:53 PM
  • The configuration is two computers connected via a router both running windows 7 home premium. It is a private network of type work as opposed to the other options home and public. I looked up domain controller ad and found it is domain controller active directory which requires some version of windows server so it is not there.

    Server: I did find the program is unable to connect because the database is not listed in whatever sql server uses (?) to keep track of created databases. Therefore I tried to add it using

    create database AtsmDatabase on (filename="c:\atsm\database\atsmdb.mdf") for attach;

    but this gives operating system error 5 access denied on the mdf file even though I running as administrator. I also checked file permissions and the admin account I am logged in with has full control permission over both the database mdf and log ldf files. Why? I am now at least able to connect to sql server using windows auth...

    Client: I wondered how one can pass login info using windows authentication. When integratedsecurity=true then user id=xyz; password=abc is ignored. Is this the reason why you need mixed authentication? The only way to connect from the client appears to be integratedsecurity=false; user id=sa or other login; password = sa or other password

    Friday, February 03, 2012 5:06 PM
  • Access Denied Error:

    When you issues any commands that need to interact with the operating system such as create database, they do not use your permissions but rather the interaction with the OS is done through the service account of SQL Server. That means if you installed SQL Server and gave it a normal user account, it has access to it's directories (security given throgh the setup) but not to anything outside of it. If you did give it a normal user account you'll either have to move the database files to a location that it does have access to (for example the data folder) or you'll have to grant it access. Could you let us know what you have for the service account for SQL Server?

    Client connectivity:

    Again, please follow teh guide above choosing to use the browser service. You will also need to follow the other guide to put sql server into mixed mode so that you can use a username and password to connect remotely. If you aren't going to use windows auth in the connection you don't actually need it in the connection string at all. To connect through sql auth you are correct, you'll need to give the username and password in the connection string. There is a connection string builder class that can help you build the correct value. The reson why sql server needs to be put into mixed mode authentication is that starting with sql server 2005, sql server was made secure by default. This means that security settings that were less secure, such as remote access and sql authentication were disabled by default upon an express install so that it wouldn't be as easy to break into, hence scure by default. For example, when a well known worm hit the internet it used a default login of SA with no password and was able to wreak havoc on the MSDE (prior to express edition) version because the people using it didn't necessarily understand security and left themselves wide open. To combat this, MS made the some of the changes mentioned above.

    -Sean

    Friday, February 03, 2012 8:05 PM
  • Server: I changed the account for sqlservr.exe from network service to local system and I was able to import the database. I set the password for the sa account. Now I get an error login failed using the connection string modified to

    Datasource=room-3\sqlexpress;initial catalog=atsmdatabase;integrated security=false;user id=sa;password=sapassword

    I can now connect using sa and sql authentiation in management studio but the program says error 18456 state 38 when opening the database. How to fix?

    Friday, February 03, 2012 9:11 PM
  • If he uses SSMS to test the login (username/password) on the server that sql server is running (local) it should connect through shared memory or named pipes, depending which are enabled for use (which I believe both are enabled on express by default). This would show that indeed the instance is running properly and the login username and password are valid for the instance. This way if he can't connect remotely we know that at least the instance is running and the login is valid so we can take that part out of the troubleshooting equation.

    -Sean

    hi Sean,

    my perplexity is about using shared memory or named pipe for the local connection, and explicitely excluding tcp/ip, that I can not understand the reason of...

    BTW, by default all network protocols are disabled at SQLExpress install time, you have to manually enable all (even at install time via command line parameters) or specifically one of them (this only at run time via SQL Server Configuration Manager)...

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
    Friday, February 03, 2012 11:23 PM
    Moderator
  • If he uses SSMS to test the login (username/password) on the server that sql server is running (local) it should connect through shared memory or named pipes, depending which are enabled for use (which I believe both are enabled on express by default). This would show that indeed the instance is running properly and the login username and password are valid for the instance. This way if he can't connect remotely we know that at least the instance is running and the login is valid so we can take that part out of the troubleshooting equation.

    -Sean

    hi Sean,

    my perplexity is about using shared memory or named pipe for the local connection, and explicitely excluding tcp/ip, that I can not understand the reason of...

    BTW, by default all network protocols are disabled at SQLExpress install time, you have to manually enable all (even at install time via command line parameters) or specifically one of them (this only at run time via SQL Server Configuration Manager)...

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/


    I don't quite undertand... you're saying all protocols are disabled when express edition is installed and running? I don't agree with that statement or else how would anyone be able to connect without first changing a bunch of settings in the configuration manager.

    My point was that shared memory in the client protocols is *generally* set to first in the order, then named pipes, then tcp. If tcp was already disabled and he did not enable it, attempting to connect remotely with the only protocol enabled of shared memory would give him errors as shared memory can only be used when on the same physical server. I wanted to take having the tcp protocol disabled out of the equation by having him test connection locally since by default shared memory is the only protocol enabled (for express edition it seems).

    To go a step further when giving the instance name in the SSMS logon screen, you can force what protocol is used to connect either using the options or putting it in the instance box directly.

    Saturday, February 04, 2012 12:17 AM
  • It sounds like the database you attached it can't find. What is the output of the following command:

    Select [name] FROM sys.databases
    

    -Sean

    Saturday, February 04, 2012 1:23 AM
  • hi Sean,

     

    I don't quite undertand... you're saying all protocols are disabled when express edition is installed and running? I don't agree with that statement or else how would anyone be able to connect without first changing a bunch of settings in the configuration manager

     

    yes, it is... at install time, the default is to disable all network protocols.. you can just locally connect via shared memory :)

     

    My point was that shared memory in the client protocols is *generally* set to first in the order, then named pipes, then tcp. If tcp was already disabled and he did not enable it, attempting to connect remotely with the only protocol enabled of shared memory would give him errors as shared memory can only be used when on the same physical server. I wanted to take having the tcp protocol disabled out of the equation by having him test connection locally since by default shared memory is the only protocol enabled (for express edition it seems).

    To go a step further when giving the instance name in the SSMS logon screen, you can force what protocol is used to connect either using the options or putting it in the instance box directly.

     

    ok...I do now understand your point... there's actually no reason to "prefer" np over tcpip, just a "guess" to test connection handshake... I thought you excluded tcpip for a "specific reason"..

    regards

     

     


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
    Saturday, February 04, 2012 2:39 AM
    Moderator
  • Gotcha, I see where I wasn't understanding, I was lumped shared memory into the networking protocols :shakes head at himself:

    Exactly, I was just doing it to troubleshoot connectivity :)

    Saturday, February 04, 2012 3:50 AM
  • Server: With Select [name] from sys.databases, I found that database was AtsmData not AtsmDatabase so when that was fixed, I was able to connect from the server. Done.

    Client: Trying to connect from the client gives  [System.Data.SqlClient.SqlException] {"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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"} System.Data.SqlClient.SqlException

    The sql server service and browser service are running on the server.

    Tcp is enabled for network configuraiton and native client configuration.

    Firewall has excepttions for sq server and sql server browser with any port allowed.

    Network has connectivity and host name recognition.

    server authentications is windows and sql server.

    ssms connections shows remote connections are allowed.

    I can connect from the server using the same connection string so therefore the instance name is correct.

    Saturday, February 04, 2012 4:26 PM
  • Could you please post the latest connection string that you are using please?

    Also try restarting the browser and sqlserver services.

    Sunday, February 05, 2012 4:33 PM
  • Same as 2/3 9:11 PM.

    Datasource=room-3\sqlexpress;initial catalog=atsmdatabase;integrated security=false;user id=sa;password=sapassword

    It works to connect on the server but not the client.  I also tried restarting the browser and sql server services.

    The error is

    +  $exception {"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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"} System.Exception {System.Data.SqlClient.SqlException}

    Monday, February 06, 2012 7:15 PM
  • It looks like you have checked for the most obvious problems. Here is my step-by-step list How to Troubleshoot Connecting to the SQL Server Database Engine 
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, February 06, 2012 9:39 PM
  • Great link Rick.

    The last thing I could think of to test is to check the errorlog for the dynamic port SQL Express is using and then running a telnet session on the client computer directing it to connect to that port. If you get an actively refused error then you know the firewall is blocking it. If you get a connection then you know the port is open.

    -Sean

    Monday, February 06, 2012 10:51 PM
  • I don't have telnet or ssms on the client. That is, typing telnet from the command prompt gives program not found.


    Connection string =
    "Data Source=192.168.0.101,49156;Database=AtsmDatabase;Integrated Security=False;User ID=sa;Password=sapassword"

    I can connect from the server with this connection string but the client gives error

    {"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: TCP Provider, error: 0 - A connection attempt failed because the connected party did

    not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"} System.Exception

    {System.Data.SqlClient.SqlException}

    If port 1433 is used on the server, the error is "No connection could be made because the target machine actively refused it."
    if port 49157 is used on the server, the error is "Timeout expired."


    http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx
    Testing the connection.
    1. On the client computer, using SQL Server Management Studio, attempt to connect using the IP Address and the TCP port number in the format IP address comma port number. For

    example, 192.168.1.101,1433 If this doesn't work, then you probably have one of the following problems:
    •Ping of the IP address doesn't work, indicating a general TCP configuration problem. Go back to the section Testing TCP/IP Connectivity.
    •SQL Server is not listening on the TCP protocol. Go back to the section Enable Protocols.
    •SQL Server is listening on a port other than the port you specified. Go back to the section Gathering Information about the Instance of SQL Server.
    •The SQL Server TCP port is being blocked by the firewall. Go back to the section Opening a Port in the Firewall.


    I believe the server firewall is configured.
    Under allowed programs, there is Sql Browser server exe and sql server windows nt. Both the name and home/work private checkboxes are checked.
    Under advance and inbound, the same names are enabled for profile private and all ports and protocols tcp and udp.

    In configuration manager, tcp is enabled for both sql server network configuration and sql native client 10.0 configuration.

    Ping 192.168.0.101 works on the client.

    The server is listening to port 49156 since I can connect to that port on the server explicitly.

    Ping from the client works, the server is listening on the correct port and the firewall is configured. What else could it be?

    I need 3 simultaneous connections to the database, that is the server and two client computers. How will that work?

    Monday, February 13, 2012 3:37 PM
  • I'm not sure I understood you. That is you said you cannot connect to192.168.0.101,49156 but you also said "The server is listening to port 49156 since I can connect to that port on the server explicitly."

    I suggest you install SSMS from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7593 Then you will be able to troubleshoot if the problem is with the general connectivity, or somewhere in the data source definition.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, February 13, 2012 4:27 PM
  • I can connect from the server using Data Source = 192.168.0.101,49156 in the connection string. I cannot connect from the client using the same data source. I found telnet from control panel\programs\turn windows features on off. telnet 192.168.0.101 49156 doesn't work from the client. It does not connect.
    Monday, February 13, 2012 5:04 PM
  • If port 1433 is used on the server, the error is "No connection could be made because the target machine actively refused it."

    This means that the firewall is blocking your connection attempts. The timeout errors also referred to could also be caused by improper firewall configuration. The next step would be to completely disable the firewall software, restart the sql server services including browser, and then attempt to connect again.

    -Sean

    Monday, February 13, 2012 5:35 PM
  • I turned the server firewall completely off for the private network but left it enabled for the public network.  Then I was able to connect from both the servier and the client simultaneously. I even changed the connection string data source to ROOM-3\SQLEXPRESS and it still could connect where ROOM-3 is the server host name. I thought I had the firewall configured to allow incoming connections which has proven to be false. How do you allow incoming connections without having to turn off the firewall completely for the private network?
    Monday, February 13, 2012 6:05 PM
  • A good overview can be watched here (generic overview but good): http://www.youtube.com/watch?v=YIUDDKeKdDY

    If you would rather read from MS (specifically for SQL Server): http://msdn.microsoft.com/en-us/library/ms175043.aspx

    Monday, February 13, 2012 6:11 PM
  • Configuration: Two computers configured as client\server, connected via a private work type network, running windows 7 home premium, 64 bit. Sql server 2005 32 bit was running

    on the server. The server host was ROOM-3 and the database AtsmDatabase. It was desired to upgrade to version 2008 R2 and to have simultaneous database connections from both the

    client and server over the private network. A .NEt C# program using the SqlConnection was used to connect to the database. The same program is used on both client and server.

    1. upgrade from sql server 2005 express to 2008 R2 express 64 bit.
    The upgrade failed in that the 2005 binaries were still running after the upgrade. After the upgrade, the only change was the addition of a folder under start\programs for sql

    server 2008 R2. The upgrade was tried a second time from the installation center and it failed because it said that no new features could be added. Therefore all of sql server

    2005 was uninstalled and a new version of sql server 2008 R2 was installed. After this the 64 bit binaries were used, which is what was wanted.

    2. After the upgrade, the database was not present in sql server so needed to be imported. I tried to import it, using the wizard but it failed with access denied. I also tried
    the sql
    "create database AtsmDatabase on (filename="c:\atsm\database\atsmdb.mdf") for attach"
    which still gave access denied. I then changed the account for sql server to Local System and then was able to import the database using sql.
    I still could not connect to the database on the server using the program. I used select [name] from sys.databases which revealed the database name was misstyped. After this was

    corrected, the database could be connected to using the program on the server.

    3. There remained the connection from the client. I downloaded and installed ssms (sql server management studio) on the server. I was told that a remote connection was not

    possible using windows authentication so I switched to using sql server authentication using the sa account. The sa account was setup in ssms. Obviously a less privileged

    account could be setup and used.
    Using ssms I found that enable remote connections were enabled.
    It was suggested to use http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx
    Therein were 4 steps.
    1. ping the server to establish network connectivity.
    Both ping of the server ip address and host name worked.
    2. sql server is not listening on the correct protocols.
    In sql server configuration manager, the sql server and sql browser services were both running. Under protocols, tcp was enabled.
    3. Make sure the server is listening on the right port number.
    The ssms log showed that 49156 was used.
    4. enable the firewall.
    Under add programs, I added both the sql server and sql browser programs for both tcp and udp protocols, for a private network and any port. The connection still could not be

    made. I looked at http://msdn.microsoft.com/en-us/library/ms175043.aspx and found that not only do the programs have to be enabled but also the ports. In advanced\inbound rules,

    I added ports 49156-49158 and then the client and server could both be connected simultaneously.

    The final connection string is  "Data Source=ROOM-3\SQLEXPRESS;Initial Catalog=AtsmDatabase;Integrated Security=False;User ID=sa;Password=sapassword";

    Sean - add whatever you see fit. I will mark your response as the answer. I am very impressed.

    Monday, February 13, 2012 7:28 PM
  • Imac,

    That's a pretty good overview of everything we checked or changed. The only additions I could make would be for explanation/clarification.

    2. The reason access was denied was due to permissions would could be added either via the security gui for the folder or though the commandline cacls command.

    3. Express edition (and named edition by default) use dynamic ports. If the sql browser service can't be reached port information through sqlbrowser will not be able to be shared and the application will probably fail to connect. Explicit tcp ports can be set through the configuration manager.

    Glad we could help you get it figured out and working!

    -Sean

    Monday, February 13, 2012 8:00 PM
  • I have almost the same problem

    I Installed Sql Server 2008R on Windows 7 Porf.

    and trying to connect to the server from my not book which running under windows 7 Home Premium, when i connect using server name like servername/sqlexpress it gives me error no 26, but when i use the ip like XXX.XXX.XXX.XXX/sqlexpress  it connects fine.

    can any one help me solve this problem please.

    Thank you very much.

    Tarik

    Sunday, September 16, 2012 10:20 AM
  • Tarik,

    If you can connect through IP (by the way it's a backslash in the instance name not a forward slash) but not through hostname then you have an issue with DNS. Open a command prompt and type in "nslookup HOSTNAME_OF_SERVER" without the quotes. If you don't get an authoritative response or the ip address differs from the one you have then you most certainly have a dns issue. Attempt to ping the hostname as well, if neither work then you'll need to check your network and dns configurations.

    The other alternative is to manually put in the entry to your HOSTS file but I would stay away from doing this.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Sunday, September 16, 2012 6:03 PM
  • Dear Sean Thank you very much for your reply.

    I did the nslookup as you instruct and i got the following result :

    Server: UnKnown

    Address: 192.168.1.1

    Non-authoritative answer:

    Name: Tarik.afaqe2e.com (where "Tarik" is my computer name and ".afaqe2w.com" is the internet provender name

    Address: 141.8.224.25

    Bw: I have DSL router connected to the switch which give me the dynamic ip address for each machine, dose this case make any differences.

    but when I ping by ip or by host name i got response and 4 Pockets sent and received, no lost pocket.

    Please help me to solve this issue.

    your help will be very appropriated.

    Again thank you very much

    Monday, September 17, 2012 10:51 AM
  • What is the result if you put in the following for the connection string:

    Tarik\sqlexpress

    I'm not sure what your network infrastructure looks like, but it looks like your default gateway is 192.168.1.1 and the other computer is 141.8.224.25 which would need to be routed to. Try putting the client and the server on the same subnet to rule out network issues.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Monday, September 17, 2012 1:48 PM
  • Thanks for your reply

    if i put the connection string as Tarik\SqlExpress i got the error number 26 server or instance error

    but if i use 192.168.1.6\Sqlexpress i connect fine.

    I have dynamic ip which assigns from my inter net provider and i can not put the ip in my connection string because it is dynamic and it always changed.

    Monday, September 17, 2012 3:11 PM
  • Tarik,

    You're having a DNS issue. In your post above you have an address of 141.8.224.25 that is returned when using DNS but the IP you're using is 192.168.1.6 obviously you should be able to see why the connection errors, it's not going to the correct machine! If you're attempting to connect from the outside and the 141 address is the dynamic IP from your ISP, then you'll have to change your network settings correctly to port forward the correct port for SQL Server through your router to the correct internal computer.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Monday, September 17, 2012 6:10 PM
  • Dear Sean

    I hope i am not Disturbing you with my case.

    but i really did not under stand how to solve this DNS issue, i am trying since 5 days to solve this issue (it drives me crazy) but i could not success, I have very small knowledge about the NET WORKING.

    Could you please kindly help me to solve this issue, i will appreciate your valuable help.

    BW. when i go to <Browse for more> in Sql Server Management studio an click the Network servers tab it shows my server name and the instance name, but when i chose it and then connect it gives me that stupid error 26.

    dos this make any sense.

    Thank you very much for your effort.

    Tuesday, September 18, 2012 1:30 PM
  • The first thing to try is to clear your DNS cache. Your computer keeps a list of the IP Addresses assigned to computer (DNS) names. If the target computer changes it's IP address, your local copy of the list might be out of date. To resolve that, you empty to list and start over.

    1. Open a command prompt window with administrator credentials.

    2. Execute the following command: ipconfig /flushdns

    Now when you attempt to connect to the target computer by name, your computer will check the local DNS cache and it won't find the computer, so it will ask the DNS controllers for the IP address. Hopefully it will get the correct new IP address and you will connect. And the new address will be stored in your DNS cache, so it will use that for future requests. If the target computer IP address changes, you might need to flush the DNS cache again.

    DNS errors can be more complex than this, but this often solves the problem.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, September 18, 2012 3:19 PM
  • Thanks to all who try to help me.

    I solved this issue by installing a second network card to the server computer.

    I make static IP's to the first network card and connect it to my work group.

    the second card i connect the DSL router to it and connect it to the internet.

    Now both working fine and smoothly i can connect to the net and stations can see my SQL Server instance.

    Special thanks to Mr.Sean who gives me the clue for this solution.

    Wednesday, September 19, 2012 11:52 AM