SQL Server 2008 R2 Remote Connection

Below are the steps to enable Remote Connection on SQL Server 2008 R2. (I am referring to the UI of Microsoft SQL Server Management Studio)

1. In Server Properties > Security, set the Server Authentication to "SQL Server and Windows Authentication Mode"
 
2. In Server Properties > Connections, check the checkbox "Allow remote connections to this server"

3. In Object Explorer tree, go to Security > Logins. Right Click on Logins and select "New Login". It will bring up following dialog:
a. Select "SQL Server authentication" option  
b. Give the desired username  and password.
c. Select the default database to connect (Optional)
d. Check different options for the user like assigning the server roles, mapping the user to a particular database, etc. Give whatever permissions you need.

[CLICK TO VIEW FULL SIZE IMAGE IN NEW WINDOW]
4. In Object Explorer, navigate to Your_Database > Security > Users. Right-Click > "New User...". It will show following dialog.
a. Click on the browse button shown with ... (ellipses) next to the Login name text box.
b. Browse and select the user you just created.
c. Assign required permissions.

[CLICK TO VIEW FULL SIZE IMAGE IN NEW WINDOW]


5. Following services must be running on the Server Machine:
a. SQL Server Browser
b. SQL Server (MSSQLSERVER)

6. In SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER, enable TCP/IP.

7. In firewall allow all inbound and outbound communications to "sqlservr.exe" (it's not sqlserver.exe) and "sqlbrowser.exe". Add them to your firewall exclusion list.

If you believe that this post can be improved in anyway or something is missing in this, I urge you to please drop a feedback on my feedback page or leave a comment on this post.

Hope this helps.

6 comments:

AKS said...

nicely posted abhijit :)

Abhijit Annaldas said...

Thanx AKS.

Anonymous said...

WОW juѕt what Ι waѕ ѕearсhing foг.
Came here by searchіng for new natok full
Also visit my weblog ... bangla funny kobita

Anonymous said...

Јust wish tο sау your агticle is as amazіng.

Τhe clеагness іn уour ρost is juѕt spectасular anԁ
i can аsѕumе уou're an expert on this subject. Fine with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please keep up the rewarding work.
Also visit my webpage local Searh engine

Anonymous said...

you're in reality a excellent webmaster. The web site loading speed is incredible. It seems that you are doing any unique trick. Moreover, The contents are masterwork. you have done a excellent task in this matter!
my web page :: ppc advertising networks

Abhijit Annaldas said...
This comment has been removed by the author.

Post a Comment