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!
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] : /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).
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?
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.
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?
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.