July 8, 2017

How to connect to SQL Server from another computer without joining a domain.

We have a small database in our office using SQL Database as backend and Microsoft Access as front-end. For the past years, we are using Windows server 2000 where the SQL Data is stored. All of the front-end workstations are connected to Windows server via a domain active directory.

If I we established a new database workstation we need to connect to a domain for our MS access Database to able connect to SQL Database via ODBC Connection.

But how to access SQL Server Database from a remote computer without joining a domain?

The simple answer is to create an SQL login and use SQL authentication to connect to SQL Server.

Use that SQL Login in your connection string, and if you're using ODBC connection just like our database use SQL Authentication instead of Windows Authentication.

To create SQL Server Login for SQL Server Authentication.

  1. In SQL Server Management Studio (SSMS), Open Object Explorer and expand the folder of the server instance (in my case its SQLEXPRESS) in which to create the new login.
  2. right-click the Security folder, point to New and then click Login
  3. On the General page, enter a username for the new login in the Login name box.
  4. Select SQL Server Authentication.
  5. Enter a password for that username.
  6. Choose the password policy options that should be applied to the new login. It's recommended to use enforcing password policy.
  7. click OK.

In my first time connecting via SQL Server Authentication, I encountered a firewall problem. So make sure the port 1433 and 1434 are open in your firewall. This two port are the ports uses by SQL Server to communicate to another computer.

To open a port in your Windows firewall.

  1. Navigate to Control Panel, System, and Security and Windows Firewall.
  2. Select Advanced settings and highlight Inbound Rules in the left pane.
  3. Right-click Inbound Rules and select New Rule.
  4. Add the port you need to open and click Next.
  5. Add the protocol (TCP or UDP) and the port number into the next window and click Next.
    1. TCP Port 1433 - sqlserver
    2. UDP Port 1434 - sqlbrowser
  6. Select Allow the connection in the next window and hit Next.
  7. Select the network type as you see fit and click Next.
  8. Name the rule something meaningful and click Finish.


No comments:

Post a Comment