How to Use Microsoft SQL Server 2008 to Connect to your Database

Accessing SQL Server Remote

Server / March 11, 2022

If you have a dedicated (physical or virtual) machine with i7MEDIA and you have SQL Server installed on the server, you can connect to SQL Server remotely by following these instructions.

Prerequisites

  • SQL Server Management Studio installed on your desktop computer.

Method 1 (Using SQL Server Authentication)

This is the simplest method but may not be desired if you need to keep SQL Server Authentication turned off or you do not wish to have multiple accounts for accessing your server (windows account, sql account).

Prerequisites

  • SQL User created on the SQL Server you wish to connect to.

Steps

  • Open SQL Server Management Studio.
  • Set the Server type to Database Engine.
  • Set the Server name to the address of your server (either IP or hostname).
  • Set the Authentication to SQL Server Authentication.
  • Set the Login to your SQL User.
  • Set the Password to your SQL User's password.
  • Click the Connect button.
  • Method 2 (Using Windows Authentication)

    This method is best if you want to keep SQL Server Authentication disabled or you do not want to have multiple accounts for connecting to your server.

    • Windows User on your server with access to SQL Server installed on your server.
  • Locate the SQL Server Management Studio shortcut on your desktop or in your Start Menu.
  • Right-click the shortcut and click the Properties option. If you're using Windows 8 and you're using the Start Menu, click the Open File Location option and then right-click the located file and select the Properties option.
  • Copy the contents of the Target (i.e. "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe").
  • Close the Properties window.
  • Open a Command Prompt and enter: runas /netonly /user:NameOfYourServer\YourServerLogin "Path Copied from Step 3" then press the Enter key.
    Example:
    • runas /netonly /user:AcmeWeb01\JohnDoeAdmin "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
  • You will be prompted for the password, enter it and press the Enter key.
  • SQL Server Management Studio will open.
  • Set the Authentication to Windows Authentication.
  • The disabled User name and password fields will look incorrect, do not worry about them.
  • Note: You can create a shortcut to the full runas command so you don't have to remember the syntax of the command. To do this, simply right-click on your desktop, select New > Shortcut and enter the runas command as in the example above for the location. Give your shortcut a meaningful name and you're good to go!

    Source: i7media.net