| Author |
Topic |
|
OOT
Starting Member
6 Posts |
 Posted - 2010-10-01 : 12:48:41
|
I have been tasked (and yes, at this point it seems a task) with moving our database from MSDE to SQL Server 2005 Express. We are running this on a Windows 7 computer. I'm no database expert so have read all the tutorials that I could find. The d/b is running properly and the EMS GUI can connect on the local machine. On a Windows Vista Laptop I've got SQL Server Management Studio Express loaded. But I can not connect remotely to that Windows 7 machine with the SQL Server 2005 Express database.Here are the settings I've used. If something is wrong could somebody please point out what I need to change. I have literally been working on this for 2 days (probably 10 hours total).The d/b was installed to allow both Windows and SQL logins.I changed the default InstanceName during installation to be: "MyCompanyNameDB"We want to connect on a specific port (14499).In SQL Server Configuration Manager, these are the settings:- Under SQL Server 2005 Services, both SQL Server (MyCompanyNameDB) and SQL Server Browser are running
- Under Protocols for MyCompanyNameDB, Shared Memory, Named Pipes and TCP/IP are enabled
- Under TCP/IP - Protocol, Enabled = Yes, Keep Alive = 30000, Listen All = No and No Delay = No
- Under TCP/IP - IP Addresses, IP1 - IP12, all are Enabled and Active, all have TCP Dynamic Ports empty (not 0) and TCP Port empty. Under IPAll, TCP Dynamic Ports is empty (not 0) and TCP Port is 14499 (the port we want everyone to connect on)
- Under SQL Native Client Configuration - Client Protocols, Shared Memory, TCP/IP and Named Pipes (in that order) are all Enabled
- Under Client Protocols - TCP/IP, Default Port = 14499, Enabled = Yes, Keep Alive = 30000 and Keep Alive Interval = 1000
In SQL Server 2005 Surface Area Configuration, these are the settings:- Under Surface Area Configuration for Service and Connections - MyCompanyNameDB - Database Engine - Service, Service Name = MSSQL$MyCompanyNameDB, Display Name = SQL Server (MyCompanyNameDB), Startup Type = Automatic, Service Status = Running
- Under Surface Area Configuration for Service and Connections - MyCompanyNameDB - Database Engine - Service, Local and Remote Connections is checked, Using both TCP/IP and Named Pipes is checked
- Under SQL Server Browser - Service, Startup Type = Automatic, Service Status = Running
I added the rules to the firewall to allow SQL Server and SQL Server Browser to both be allowed. That still didn't work. Right now I have the Firewall turned off (private and public). At least I think I do ... I'm new to 7. I have checked Turn Off Windows Firewall for both so I think it's off. But I still can't connect from my laptop to the server. Every time I made a change I restarted the SQL Server. With MSDE it was so easy (EnableNetworkProtocols = 0 and change default port using svrnetcn). But with 2005 I am completely lost on what I'm doing wrong or what I have forgotten to change.   Any idea's, suggestions, examples, help, etc is greatly appreciated. |
|
|
jleitao
Posting Yak Master
100 Posts |
Posted - 2010-10-01 : 12:55:00
|
| i'm not a expert but i know that exists a "allow remote connection to this server" propreties that i dont see in your post. It is enable?In management studio. Right click on the server name -> properties -> connections |
 |
|
|
OOT
Starting Member
6 Posts |
Posted - 2010-10-01 : 12:59:56
|
quote:
Originally posted by jleitao
i'm not a expert but i know that exists a "allow remote connection to this server" propreties that i dont see in your post. It is enable?In management studio. Right click on the server name -> properties -> connections
I don't have Management Studio on that computer. The 2005 Management studio doesn't load on Windows 7. The 2008 version wants to install SQL Server 2008. If there is another version that doesn't require SQL Server 2008 to be installed I haven't been able to find it. Is there another way of doing that without using Management Studio? I'll start looking again for a download of only Management Studio in the meantime. |
|
|
|
OOT
Starting Member
6 Posts |
Posted - 2010-10-02 : 22:08:53
|
| OK. I finally found the correct program to download from MS. I installed the 2008 SQL Server Management Studio. Once installed, I went to the connections for the database and it has it listed as accepting remote connections. So it appears that part is correctly configured. Can anyone see anything in the other settings listed above that would be causing this problem. I can't understand the problem unless there is something with the firewall that is still running (even though both settings say it is off).Any other suggestions or ideas? Anything? All help is appreciated. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-02 : 22:43:16
|
| You say you changed the default instance name...this doesn't sound right. Default instance will have the name of the computer. Sounds like you created a named instance.This means that you connect to it as machineName\instanceName. Come to think of it, isn't all SQL Express installations named instances? I could be wrong here as I use EE and SE not Express.Please tell us exactly what error message you receive when you try to connect remotely?And how are you connecting?I'd like to see the answers to that 1st, but if it is an alias problem, try using cliconfg.exe instead of SQL Configuration Manager. |
|
|
|
OOT
Starting Member
6 Posts |
Posted - 2010-10-03 : 20:41:24
|
quote:
Originally posted by russell
You say you changed the default instance name...this doesn't sound right. Default instance will have the name of the computer. Sounds like you created a named instance.This means that you connect to it as machineName\instanceName. Come to think of it, isn't all SQL Express installations named instances? I could be wrong here as I use EE and SE not Express.
You are correct. I did create an named instance. The default name was SQLExpress (I think). I changed it to MyCompanyNameDB. I also put the IPAll port set to 14499.quote:
Please tell us exactly what error message you receive when you try to connect remotely?
This is the error message I'm getting:quote:
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 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error 10061)
I've tried finding help using the error number but they all say go through the things I did when I initially set it up. Maybe I missed something???quote:
Originally posted by russellAnd how are you connecting?I'd like to see the answers to that 1st, but if it is an alias problem, try using cliconfg.exe instead of SQL Configuration Manager.
I attempting to connect from my house using using SQL Server Management Studio 2008. I also installed that on the machine at the office to make sure that it was setup to allow remote connections. I'm using the IP Address\MyCompanyNameDB, 14499, user: sa and the password. That error above is what I get. If I remove the ,14499 I get the same error.I'm pretty sure that it has to be something with the SQL Server because I *believe* I've got the firewall totally turned off. Not sure what else would be denying the connection. Here's the firewall screenshot: I don't believe I'm using an alias as I haven't set one up (that I know of).Does that help any more? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
OOT
Starting Member
6 Posts |
Posted - 2010-10-04 : 11:58:54
|
quote:
Originally posted by tkizer
Do a quick telnet test:telnet ServerName PortNumberRun that from the client and direct it to the database. If it errors, then something on the server is preventing the connection.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
OK. That failed also. If I specified a port it said "Could not open a connection to the host, on port 14499." If I didn't specify the port it said the same thing but port 23. So it looks like it's something on Windows 7 and hopefully not the SQL Server. Back to the work computer.Thanks for the suggestions. I'll keep looking and if I find anything I'll post it here in case anyone else ever has an issue like this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Problem with remote connections