Sql-server – Cant connect to sql server database using ip

databasesqlsql serversql-server-2012

I've created a sql server database on server A. I was always using Windows Authentication to connect, but now I want to connect to database from server B. Then, I added a new user to database and turned Sql Authentication ON.

Now, I can connect to database from server A using:

1 ) Windows Authentication
-Server name: PC\SQLEXPRESS

or

2) SQL Authentication
-Server name: localhost\SQLEXPRESS,1433
-Login: new_user
-Password: example

But I cant connect with my public ip which is for example: 133.155.84.28. I have to use localhost. That means I can connect with database from server "B".

I went to SQL Server Configuration Manager/Protocols for SQLEXPRESS/TCP IP/IP Adresses. Here you can see which ip adressess let me login to my local SQLEXPRESS:
Picture

I can't connect using neither 133.155.84.28 nor 127.0.0.1. Only localhost. What should I do?

Best Answer

As can be seen in the SQL Server Configuration Manager utility, none of your actual IP addresses have been activated.

IP Configuration

You have to set the following option for the IP addresses you want to connect to:

Enabled           : Yes

Port Configuration

If you want to connect to the default 1433 port, then you would also have to set the following:

TCP Dynamic Ports : 
TCP Port          : 1433

Leave the Dynamic Port blank/empty and then Apply/Close the Configuration Manager. You will have to restart the SQL Server for these changes to be applied.

Edit:
I just realised that you probably have the following option set on the initial screen of the SQL Server Configuration Manager utility for the TCP/IP properties in the Protocol register:

Listen All        : Yes

Which basically means my answer might not be helpful unless you specifically set Listen All : No and then activate the IP address in the IP Addresses register.

If you don't see your public IP Address in the SQL Server Configuration Manager in the IP Properties, then SQL Server is unaware of your public IP.

Related Topic