First Step
To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio)
In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
Right-click the Security folder, point to New, and then click Login.
On the General page, enter a name for the new login in the Login name box.
Select SQL Server Authentication. Windows Authentication is the more secure option.
Enter a password for the login.
Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.
Click OK.
Enabling TCP/IP protocol
The first step is to enable TCP/IP protocol on the SQL Server service. Open the SQL Server Configuration Manager in the Configuration Tools folder under the Microsoft SQL Server folder:
From the SQL Server Network Configuration node, select the Protocols item for the appropriate SQL Server. In the details pane, right click the TCP/IP item and select the Enable option:
After this step the Warning box pop up in which informs us that changes that are made won’t take effect until the next time service is started.
In order for the changes take effect, from console pane, select the SQL Server Services and from the details pane, right click the SQL Server (SQLEXPRESS) database engine instance and click the Restart option:
Now the service started with TCP/IP enabled, but still can’t connect remotely until we configure the Windows Firewall.
Configuring Windows Firewall
From the Control Panel choose Windows Firewall and click the Advanced settings or just type wf.msc in Search program and files from the Start menu:
- Inbound Rules: You can allow or block traffic attempts to access the computer that matches certain criteria in the rule. By default inbound traffic is blocked, to allow traffic to access computer, you need to create inbound rule.
- Outbound Rules: You can allow or block traffic originating from specifying computer creating rules. By default outbound traffic is allowed, so you need to create the rule that block outbound traffic.
In the Windows Firewall with Advanced Security, click the Inbound Rules from the left pane, right click Inbound Rules and select the New Rule or from the Actions pane click the New Rule:
On which you will click, it’s up to you. In both case the New Inbound Rule Wizard will appear. Under the Rule Type choose Port and click the Next button:
From this link you can more informed of all rule types which are shown in the image above.
In the Protocols and Ports there are a several options that you can choose, depending on which type of protocols you select.
If you are wondering what is the difference between the TCP and UDP protocols you can find from this link.
As we mentioned at the beginning of the article TCP is used for the default instance and named instance if is the only instance installed on the machine and default port is 1433.
- All local ports: Rule applies on all ports from the selected protocol.
- Specific local ports: In the text box you specify a port or set of ports to which the rule applies.
For this example, select the UDP protocol and in the Specific local ports enter port number 1434. To proceed with the settings SQL Browser services, click the Next button:
- Allow the connection: Includes all connections secure and insecure.
- Allow the connection if it is secure: Includes only connection if it is made through a secure channel.
- Block the connection: Blocks all connections secure and insecure.
In the Action dialog choose Allow the connection and click the Next button:
- Domain: The setting is applied only when a computer is connected to a domain.
- Private: The setting is applied when a computer is connected to a network that is identified as a private network.
- Public: The setting is applied when a computer is connected to untrusted public network.
In the Profile dialog choose all three profiles and click the Next:
On this step give the rule a name and click the Finish.
Note: When we give the name of the rule, please write some descriptive name that you can understand later, when you need to find them or edit in the Inbound Rules list.
Now create an allow rule for the database engine instance.
Go to the New Rule and from the Rule Type select the Custom rule:
In the Program under the Services click the Customize button:
From the Customize Service Settings under Apply to this service select database engine instance service and click the OK button:
Then click the Next all the way to the Name dialog, give rule a name and click the Finish:
Now when all rules are set up, you are ready to connect to remote SQL Server.
Start the SQL Server, in the dialog window for the Server name enters the name of the instance that you want to connect with. From the Authentication drop down box, select the SQL Server Authentication and for the field Login and the Password enter your credentials then click the Connect button.
Second Step
To be able to connect to the SQL-DB on your Virtual pc do these steps:
1. MS-SQL-Server
open the terminal and run this command
netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT
2. MySqlopen the terminal and run this command
netsh firewall set portopening protocol = TCP port = 3306 name = MYSQLPort mode = ENABLE scope = SUBNET profile = CURRENT
finally run this query in php-myadmin or any query app you use:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '';
Hint:
If on creating a login got this message :
To delete a login from sql server use this queries as needed:
DROP login username;
GO
DROP useer username;
GO
EXEC sp_change_users_login 'Report'
GO
EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'pass'
GO
USE db-name
EXEC sp_dropuser 'username'
GO
Another problem :
do not forget to configure your sql-server to allow mixed authentication.
Here are steps to fix:
- Right-click on SQL Server instance at root of Object Explorer, click on Properties
- Select Security from the left pane.
Select the SQL Server and Windows Authentication mode radio button, and click OK.
Right-click on the SQL Server instance, select Restart (alternatively, open up Services and restart the SQL Server service).
.GIF)
Safwan Al Najjar
