I am a newbie using SQLExpress 2014 on Windows 10 machine. Trying to access the backend database for the membership database for a local non-profit society using ODBC. I copied the SQLServer data files onto my home computer where I had installed SQLExpress, and have successfully used ODBC to read the data when confined to the single computer. Next step is to access the data across my home network from a second Windows machine. So far, the SQL Server is invisible from the second machine.
I downloaded and installed a utility called SQLPing v3.0. Using the server machine, I confirmed the server was running, and that it was using IP port 64434. I opened that port, plus port 1433 on the Windows firewall, then tried running SQPPing on the second machine with no success. Similarly, I had no success trying to create a DSN connection from the second machine to the server machine. I can successfully Ping from the second machine to the Server machine, so I know the computer itself is visible across the network.
One thing I noticed is that SQLPing runs very quickly on the second machine (and reports that it finds no servers), whereas it proceeds slow enough when running on the server machine that I can see each IP address as it attempts a connection.
I have not configured anything about file-sharing on the Server machine; I assumed that than installing SQL Server was sufficient, but correct me if I'm wrong. As a newbie, I am completely stumped. Any advice how to proceed would be appreciated.
How is SQL Express configured? IIRC, out of the box it looks like this (SQL Server COonfiguration Manager:
If you're not allowing TCP/IP connections (like me, mine is disabled) you won't be able to connect
1 Like
Thanks for your help. I have confirmed that TCP/IP is enabled.
Since writing my original post, I discovered some troubleshooting tips (https://knowhow.visual-paradigm.com/hibernate/solving-sql-server-connection-problem/). Near the bottom of the page is advice to telnet into the SQLServer which will respond with a blank screen. That's exactly what happens. Furthermore, changing the SQLServer to listen on a different port returns a "cannot connect" telnet message. This confirms that something is listening on the assigned port that is acting with SQLServer's expected behaviour.
I have three ideas left to explore, other than I might be overlooking something in the SQLServer configuation:
-
I've created a Login on the SQLServer and use those credentials for the connection. Perhaps I am doing that incorrectly. I haven't tried connecting with the SA account. Is it a simple as creating a Login using the SQLServer Management Studio, or is something else required?
-
perhaps there is something corrupted on the second computer, and its SQL protocol is not working properly. I will try another computer.
-
perhaps there is something corrupted in the SQLServer installation. This is unlikely since I have installed/removed/installed it several times.
Any other ideas or suggestions are welcome.
Can you try to connect using SSMS or SQLCMD? You might possibly get more info on problems. At least narrow it down to reachability or authentication.
Is the account you want to use a Windows account or SQL Server account?
Making good progress, although it's a case of "which of these several changes actually fixed the problem". I used a different SQL driver on the client computer, reset the password and other configuration on the SQLServer login account, and some other stuff that I don't recall. I think the actual fix was to use the driver labelled "SQL Server" instead of the one labelled "ODBC for SQL Server".
Bottom line is that I've created an ODBC connection to the database that reports it is successful. Unfortunately, this second computer (my wife's) does not have any familiar software that I can actually retrieve some data from the database, so at this point, I am just assuming that it's functional.
Thanks for your help. I appreciate your stepping up for a noobie.
1 Like