How do I access SQL Server installation from another machine in my home network?

hpsuser
hpsuser used Ask the Experts™
on
Hello,

I have a SQL Server 2005 installed on my LENOVO machine.  On the LENOVO, within Excel, I can write queries and pull data into Excel from SQL Server.

What I would like to be able to do is perform the same functionality within Excel, but from another machine (PAVILION) on my home network.  This PAVILION machine can access my wireless network already, and can see a shared folder I set up on the LENOVO.  

However, when I try the following in Excel (DATA -> GET EXTERNAL DATA -> FROM OTHER SOURCES -> SQL SERVER -> FROM SQL SERVER) and enter in the Server Name and User Name and Password I get an error message saying access is denied.  

The above test in Excel I mentioned that caused an error, that was on the PAVILION machine.  When I try the exact same thing from the LENOVO machine it works fine.

Any help would be greatly appreciated.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
Are you trying to create a new ODBC Connector?

If so..  You are going to need to create a new account to access your SQL server or use the sa account with a password.

HTH,

Kent

Author

Commented:
Thanks Kent.  

When you ask if I am "trying to create a new ODBC Connector", I think the answer is yes (I am trying to connect to my database from a new machine on my home network.)

I have created a new USER/PASSWORD on my SQL Server, and have tested that it works from the machine that SQL Server is installed on.

What I cannot get to work is being able to access SQL Server from another machine, and I'm not sure what I need to do.

Please let me know what other info I can provide.

Thanks
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Make sure remote connections via TCP/IP are allowed. Depending on your MSSQL Edition they are disabled by default. To check, start the SQL Server Surface Configuration and go to Remote Connections. You should have ticked "Local and Remote Connections", "TCP/IP only" (or "TCP/IP and Named Pipes").
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
The default is Windows Authentication which works fine on the same machine.  But for remote access you not only need to set up a user with SQL Authentication but you also have to enable TCP/IP access (if you are not in a domain that can provide Windows authentication).

Author

Commented:
This worked great...I made the changes using the Surface Area Configuration Tool to use "TCP/IP and Named Pipes", restated a few SQL Server services, and now I am able to query from the SQL Server on the new machine when I was getting connections errors beforehand.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial