0

I'd like to connect to a local instance of SQL Server on another machine within the same network, and am wondering if it's even possible.

For example, say we have Machine01 and Machine02, both on the same internal network. I have several IIS websites setup on Machine01. If I log onto Machine02 and query the URL of one of the websites on Machine01 in in internet browser (say http://Machine01:9000), the website will load just fine. This has me thinking that it might be possible to construct a connection string to access the local database on Machine01 from Machine02. Is this possible?

I have Allow remote connections to this server checked in SSMS.

I've tried this connection string:

Server=Machine01;trusted_connection=true;Database=MyDB;Persist Security Info=True
BenR
  • 2,791
  • 3
  • 26
  • 36

2 Answers2

1

Your connection string is correct in that the Server is the target computer name, and the premise of what you're trying to do is certainly correct and quite possible.

However make sure that the firewall on Machine01 is set to allow inbound traffic on the LAN from the SQL Server port (by default, 1433).

Also, the trusted_connection bit may not work, depending on how you have your users set up. If the user account on Machine02 a trusted user account on Machine01? If in any doubt, set up a SQL Server user account on your Machine01 SQL server, make sure you have SQL connections enabled (Server properties -> Security -> SQL Server and Windows Authentication mode), and pass across the SQL user name and password in the connection string instead...

PulseLab
  • 1,577
  • 11
  • 15
  • Thanks, you've got me headed in the right direction. I make a connection to the DB if I make the connection string `Server=Machine01,1433;Database=myDb;User Id=myUserId;Password=myPassword;`. Now I'm getting an error about wrong password, which is a different problem, but at least I'm connecting. Thanks @PulseLab – BenR Jun 16 '14 at 20:22
  • No probs, is the user id and password you're passing in a SQL login you've created in SSMS on Machine01? – PulseLab Jun 16 '14 at 21:09
0

You might try setting up a linked server, I find it to make the queries much easier. Here is some information on how to set it up.

chemnteach
  • 375
  • 8
  • 23