question

TiltingCode avatar image
TiltingCode asked

Use Management Studio to access SQL DB on another machine

We have SQL Server on another machine installed by a person who has left the company. I believe it is 2008 R2 as that is what we use. SQL is required on this machine which is used by non-technical people so SQL Server Management Studio was probably removed to prevent the non-technical people from playing around. I need to access the DB and would appreciate some ideas as to what to do? Can I make the folders on the other machine shareable and open the DB with Management Studio from another PC? If so, how do I do that? Or should I just install Management Studio on that machine and delete it every time? Thanks for your help!
sql server 2008 r2management studioremote access
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
You can use SSMS to connect to this machine from any other machine in the same network. When you launch SSMS, it prompts you for connection information. Just supply the machine name and appropriate credentials and you'll be able to connect to the instance. ![alt text][1] [1]: /storage/temp/3702-ssmsconnectionwindow.png One caveat is that the instance does have to be listening on the network. If that is not already enabled, you can use SQL Server Configuration manager on that machine to enable it (requires restart if you need to make that change).

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply, KenJ. There are currently people who have presedence using the other computer and I cannot access it but I have additional questions\concerns. When I looked up SQL Server Configuration Manager and connecting remotely I found that I have to have SSMS installed on the source computer to "allow remote connections". Is this true and if so what do I do to correct it without SSMS on the remote computer? For all I know this won't be an issue but like I said there are currently people using the other computer so I figured if this is the case I'll be prepared......
0 Likes 0 ·
TiltingCode avatar image
TiltingCode answered
Thanks again, KenJ. Looking at Configuration Manager do I have the IP address of the source computer (where the DB resides) as an IP Address or the remote computer (From where I want run SSMS)? I researched this on the internet and some the descriptions don't make sense to me. And those different IP1, IP2, IP3... figures. do those go from top down till something works or until none of them work?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Guys, I think a few wires are getting crossed here. Enabling remote connections is configured in SSMS, not Configuration Manager and it only controls whether stored procedures can be executed from a remote machine, not whether connections can be accepted from remote machines ( https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SQL13.SWB.SERVERPROPERTIES.CONNECTIONS.F1)&rd=true). This option is on by default as I recall so, as long as you have some credentials that have permissions on the SQL Server instance, you should be able to connect from SSMS on another machine. You don't need any IP addresses, just use the name of the machine that's hosting the SQL Server, fill in your credentials and try the Connect button.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

good point. i'm removing my comment since that was completely unrelated to having the instance listen on the network which was the thrust of my answer.
0 Likes 0 ·
TiltingCode avatar image
TiltingCode answered
Thanks David, yes, I understand that remote connections is enabled through SSMS, however, SSMS is not loaded on the machine which hosts the DB. So, I have tried connecting to SQL Server from another machine on the network using SSMS and have gotten that typical message where it says "The server was not found or was unaccessable". Could this be due to our companies firewall or something else related to security?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
It sounds like either your credentials are no good or the SQL ports aren't open throught that machine's firewall. Both will involve you getting onto that machine. For the firewall, if it's just a single default instance of SQL you probably just need port 1433 open. If not, you can find more information here: https://msdn.microsoft.com/en-us/library/cc646023(v=sql.105).aspx. You could test your credentials with a .UDL file. Create a new text document. Rename it to test.udl. Double click and configure your connection and credentials settings and test it. You might want to start on the actual machine to eliminate the firewall. Get it working there. (Don't leave that file lying around on the other machine with your credentials saved in it though!) Then copy that file to your own machine and double check that it's working from there too.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.