HOW TO ACCESS MS SQL IN WINDOWS SERVER FROM A REMOTE SQL SERVER MANAGEMENT STUDIO

It is always troublesome to access MS SQL running in windows server from a local system. In this post I am explaining how to do it easily in less time. The main things to be done are: First we need to allow remote access to the sql server we are using.Then set a port to access the sql server through TCP/IP connection. Next step is to create an inbound rule in firewall to accept connection to that port. You can follow the steps given below to do this.

1. Connect with windows server
2. Open sql server management studio. And connect to the sql server instance.
3. Right click on the SQL server instance and select properties.
4. Select "Connections" from side menu. Make sure the "Allow remote connections to this server" Checkbox is selected. then press OK. 

5.  Open "SQL server configuration manager"
6. In the side menu expand "SQL server network configuration"
7. Double click on the SQL instance name.
8. Ensure that "Shared memory" an "TCP/IP" are enabled. If not right click and enable it.

9. Double click on the TCP/IP. A popup with two tabs will appear.
10. Open tab "IP addresses" and scroll down to IPAII section. clear values in "TCP dynamic ports" section. add port number to "TCP port" section and Apply changes.

11. Now select Sql server services from side bar. From the services list restart the SQL Server instance to apply changes.
12. Open "firewall with advanced security" from control panel.
13. Select Inbound rules from side panel. and select "New Rule" from right side.
14. First in Rule Type select "Port"
15. In protocol and ports select TCP" protocol. And enter your port number below after selecting "specific ports"
16. Select all three options (Domain, Private, Public) under "Profile"
17. In the "Name" section give a name and description to the rule then finish.
Now everything is set. Close the window server and in the local system open sql server management studio. In server name enter IP address of windows server and the port number separated by a coma(,) and enter username and password.

 now the sql server will connect.

For detailed video please refer the following




Comments

Post a Comment

Popular posts from this blog

FIX: Severity Code Description Project File Line Suppression State Error Web deployment task failed.

Image
"Severity Code Description Project File Line Suppression State Error Web deployment task failed. An error occurred when the request was processed on the remote computer.) An error occurred when the request was processed on the remote computer. The server experienced an issue processing the request. Contact the server administrator for more information".             This error occurs when first time hosting to a IIS website using WebDeploy option in visual studio. When WebDeploy option is setup for a new website is created on IIS it will create a user for deploying. This error is causing because the created user does not have the sufficient permissions to access the website directories.              We can add this permissions manually by login in into server. The following steps are used to solve the error. login in to the windows server open IIS (Internet Information Service) Manager Select the website needed to deploy Right click on the website and select
Read more

FIX Web Deploy from visual studio failed with error ERROR_EXCEEDED_MAX_S...

Image
I had recently come across an error when hosting a web application. I was using web deploy method from visual studio 2017. When deploying to windows server 2012 i got error saying ERROR_EXCEEDED_MAX_SITE_CONNECTIONS. the complete erro message is as follows: " Severity Code Description Project File Line Suppression State Error Web deployment task failed. (The maximum number of connections for this site has been exceeded. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXCEEDED_MAX_SITE_CONNECTIONS.)"                   I get a solution after searching in internet for hours. So I thought if I make a post on it, it will be helpful for other and of course for me when same error come again. The following steps are used to solve the error. In window server open "Services" Find "Web management service" Right click in "Web management service"  Restart the "Web management service" Now try to deploy the web applic
Read more

Add/Remove automatic password expiration of MS SQL user

Image
MS SQL has a automatic password expiration feature for database users. Which has both advantage and disadvantage.  Advantage is that it provides security. When we use the same password for same time, it makes it vulnerable to attacks. But when we implementing automatic password expiration feature we should change the database password in particular intervals. This will be difficult if the SQL server is less maintained or database user is accessed from multiple remote locations. So depending on our requirement we can turn ON or OFF this feature. The following step the following steps will help you to enable or disable automatic expiration of MS SQL user password. 1. open the SSMS and login using SQL server authentication 2. Expand the 'Security' folder in object explorer 3. Expand 'Logins' folder under 'Security' 4. All users will be listed in the 'Logins' folder. Now double click on the username we to change 5. On the popup window we can see &#
Read more