Remote Access to SQLEXPRESS (not the obvious solution!)
-
Dear all,
Wondering if someone can help me out here. I took on dev for an app that requires remote access to an SQLEXPRESS database. The environment is usually non-domained, so I use Mixed Mode auth.
On many installs (not all, but maybe 1 in 3) I can get no access to the database from remote machines - they're not even detected.
Steps tried:
1. Enable & start SQL browser service on server.
2. Enable Remote Connections in SSMS and restart server (also tried disabling, restarting, enabling, restarting)
3. Enable TCP/IP & Named Pipes in SQL Server Configuration Manager. Restart db service.
4. Set TCP/IP listening port to 1433 static rather than dynamic. Restart db service.
5. Enable TCP/IP and Named Pipes in SQL Server Client Network Utility on remote machine (cliconfg.exe)
6. Add sqlservr.exe and sqlbrowser.exe as exceptions to windows firewall on server (saw this on a forum post)
7. Disable TCP/IP v6 on remote machine (trying to keep things simple)
The server is still remaining completely invisible from the remote machine. I have of course tried pinging the remote machine and it responds correctly. Don't have telnet (both machines are Vista) but could always install that if I thought it'd help for testing.
Problem occurs with both named instances and default instances.
Please could someone give me a pointer on this? I have been through _so_ many forum posts!
Thanks,
Al
-
Meant to add - also ran xp_readerrorlog and noticed a comment about needing to run RECONFIGURE to apply the Remote Connections setting change (0=>1). Slightly strange, as did that through SSMS and restarted DB server, but decided to humour the DB and ran RECONFIGURE then restarted db service. Message disappeared. Problem not fixed.
-
Al the SQL Browser Service is what makes a SQL instance "visible", but that is not required. i don't think it's included in express versions, anyway.
It sounds like you already did all the ground work of firewalls and allowing remote connections and everything.
when you try to connect remotely, you know the machine name and instance name already, right? without trying to "find" it on the network, can't you just type in the machine\Instance name?
for example DEV223\SQL2008R2 happens to be one of the instances on my machine,and that's what i would type in to try and connect with;
alternatively, you could try it via IP\Instancename, or Ip,port
like this:
DEV223\SQL2008R2
192.168.1.55\SQLEXPRESSR2
192.168.1.55,1719
*Edit: for the port, you'll want to change the dynamic port to a static one:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
@lowell, thanks for the reply.
The SQL Browser service is included in the express edition (at least, it is in the 2008 release). While not technically necessary (esp if using static ports), I found that on some high-lag networks (e.g. dodgy wireless) requests against the server time out unless the browser service is enabled.
As regards your screenshot, that's what I did for my Step 4 - when that didn't work I also tried setting the static port manually for every IP listed, and enabling every IP listed. No joy.
I didn't get a chance to install SSMS on the remote machine, so couldn't try manual connection details, as I had nowhere to enter them (in retrospect, maybe I could have tried to create an ODBC data source). The software I was installing does a scan for SQL instances using the .NET SqlDataSourceEnumerator object. It correctly identified the local SQL servers plus a remote server on another machine, but nothing on the remote machine I want to connect to.
Kind regards,
Al
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply