question

NazHim-9882 avatar image
0 Votes"
NazHim-9882 asked NazHim-9882 edited

how to connect Remote SQL Server Database . through the internet using Public Static IP and Port Number..?

Hi All,
how to connect Remote SQL Server Database. through the internet using Public Static IP and Port Number..?
in remote machine. i am used SQL Server 2019.
i want to connect remote SQL Server Database. with using public static IP 126.158.2.125 and Port 50121.
from Local Machine.
i am created port with number 50121.
created firewall Rules also. like image at below.

in Windows defender firewall
TCP Inbound Rules.
120885-2021-08-05-213729-01.png
120830-2021-08-05-213856-02.png
120894-2021-08-05-213926-03.png
120921-2021-08-05-214200-04.png
120856-2021-08-05-220952-ib-05.png

UDP Outbound Rules.
120932-2021-08-05-220358-ob-01.png
120857-2021-08-05-220451-ob-02.png
120895-2021-08-05-220533-ob-03.png
120896-2021-08-05-220607-ob-04.png
120897-2021-08-05-220734-ob-05.png

Connection Local:
it's working fine.
used Connection string like at below.
Server Local Machine IP: 192.168.1.17 (sample)
Port Number: 50121.

 SqlConnectionStringBuilder sqlConBui = new SqlConnectionStringBuilder();
 sqlConBui.DataSource = @"192.168.1.17,50121\STUDENTMSSQLSERVER";
 sqlConBui.InitialCatalog = "StudentDatBas";
 sqlConBui.PersistSecurityInfo = true;
 sqlConBui.MultipleActiveResultSets = true;
 sqlConBui.UserID = "Student";
 sqlConBui.Password = "Stu1234";
 string connectionString = sqlConBui.ConnectionString;
 try
 {
     SqlConnection con = new SqlConnection(connectionString);
     con.Open();  // Open the connection
 }
 catch (Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

i want to Access SQL Server DataBase from Remote machine. through the internet.
with using public static IP Address and specific port number.

Remote Connection:
it's not working.
used Connection string like at below.
Public Static IP: 126.158.2.125 (sample)
Port Number: 50121.

 SqlConnectionStringBuilder sqlConBui = new SqlConnectionStringBuilder();
     sqlConBui.DataSource = @"126.158.2.125,50121\STUDENTMSSQLSERVER";
     sqlConBui.InitialCatalog = "StudentDatBas";
     sqlConBui.PersistSecurityInfo = true;
     sqlConBui.MultipleActiveResultSets = true;
     sqlConBui.UserID = "Student";
     sqlConBui.Password = "Stu1234";
     string connectionString = sqlConBui.ConnectionString;
     try
     {
         SqlConnection con = new SqlConnection(connectionString);
         con.Open();  // Open the connection
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }

getting error.
like image at below.

120916-2021-08-05-224729-er-01.png

Port Result:
120972-2021-08-06-000829-pr-01.png

SQL Server Image:
120838-2021-08-05-225259-sql-server-01.png
120900-2021-08-05-225410-sql-server-02.png
120839-2021-08-05-225541-sql-server-03.png

SQL Server Configuration Manager:
120952-2021-08-05-230306-scm-01.png
120924-2021-08-05-230442-scm-02.png
120903-2021-08-05-230810-scm-03.png
120877-2021-08-05-230920-scm-04.png
120850-2021-08-05-231113-scm-05.png

how i can do it..?.
can provie some code snippet..?.

with best Regards.
NazHim


sql-server-generaldotnet-csharpsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Maybe it is a good thing that it is not working? Not the least now that you have announced the IP-address and port number here?

Permitting access to SQL Server over the public internet is rarely a good idea. Try setting up a VPN connection instead.

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,

sqlConBui.DataSource = @"126.158.2.125,50121\STUDENTMSSQLSERVER";

Please try "126.158.2.125,50121"

network-related or instance-specific error occurred while establishing...

This error usually means that the client can't find the SQL Server instance.

If you cannot connect SQL Server through your application, please confirm whether the network and port is work fine, and try to use SSMS to remotely connect to the SQL Server instance for testing to explore where the problem may be.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NazHim-9882 avatar image
0 Votes"
NazHim-9882 answered ErlandSommarskog commented

hello Cris Zhan-MSFT
thanks for your fast response.

i am tried. 126.158.2.125,50121 (IP is sample not real)
bad luck. not working it.

regards
NazHim

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Try using a colon instead of a comma:

126.158.2.125:50121

0 Votes 0 ·
NazHim-9882 avatar image NazHim-9882 BonnieDeWitt-QnA ·

hello BonnieDeWitt-Qna

i am tryed. but bad luck.
not working. getting error..?

121591-screenshot-2021-08-09-123325-er-03.png



regards
NazHim

0 Votes 0 ·

I can see your Deleted comment and I see that you're using port 1433 in your connection string. That is not necessary, because 1433 is the default port that SQL Server uses.

The error message screenshot says that your connection string is not valid, so that may have something to do with your problem.

I wrote a blog post many years ago that shows an easy way to figure out what connection string to use. It involves using a .udl file, which I explain in my blog. Take a look at it here:

https://geek-goddess-bonnie.blogspot.com/2015/02/easy-peasy-connection-strings.html


0 Votes 0 ·
Show more comments
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

you opened the firewall on the sqlserver windows box, but did you enable port forwarding from its modem?


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BonnieDeWitt-QnA avatar image
0 Votes"
BonnieDeWitt-QnA answered BonnieDeWitt-QnA edited

Here is a sample connection string, @NazHim-9882 :

 "server=TheIPAddress;uid=TheUser;pwd=ThePassword;database=TheDatabase"

Replace your values in the string.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NazHim-9882 avatar image
0 Votes"
NazHim-9882 answered BonnieDeWitt-QnA commented

Hello BonnieDeWitt-QnA

i am using C# windows form application

regards
NazHim

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OK (I didn't ask about that, but that's OK).

You can use the app.config to store your connection strings, which then becomes the MyWindowsApplication.exe.config, once it's compiled. The .exe.config an be modified if/when you need to change your connection string.

Then you use the ConfigurationManager to access it from your Form (or from anywhere else in your application). Here is a sample config settings:

 <connectionStrings>
   <add name="MyConnectionString" connectionString="server=TheIPAddress;uid=sa;pwd=ThePassword;database=TheDatabase"/>
 </connectionStrings>  

And here is how you use the ConfigurationManager to get the connection string:

 ConnectionStringSettingsCollection ConnectionStrings = ConfigurationManager.ConnectionStrings;
 string myConnString = null;
 if (ConnectionStrings != null && ConnectionStrings["MyConnectionString"] != null)
     myConnString = ConnectionStrings["MyConnectionString"].ConnectionString;







0 Votes 0 ·