Internet Connection to SQL Database

[TheWizardofInt]

On the LAN, I can make the SQL Server database=192.168.0.5, which is where IIS is running and the SQL Server is running

 

This doesn't work over the Internet, when I change the IP address to its remove IP. It is making a connection to the Server, but not to the SQL server (invalid database name or incorrect connection error).

 

Am I out to lunch thinking I can do this, or is there a trick to it I am missing?

[Robby]

Where is the application being hosted and where is SQL server hosted? Is one local and the other on a hosted server?

[TheWizardofInt]

Yes, one is local (running on your PC) and SQL is running on a Windows Advanced Server with a static IP, where IIS is running and the SQL database is located.

[Robby]

What does your connection string look like (Place *** over you username and password)

 

Is this an intranet setup or will it serve the public?

[TheWizardofInt]

sServer = 1.2.3.4

sqlConn = New SqlClient.SqlConnection("Server=" & sServer & ";Database=" & _
           sDir & ";uid=" & sGlobSQLUser & ";pwd=" & sGlobSQLPass & ";")

       dim dt as New DataTable
       sqlDA = New SqlClient.SqlDataAdapter(sSQL, sqlConn)
       Try
           sqlDA.Fill(dt)
       Catch ex1 As Exception
           sannounce = ex1.Message
           dt.Dispose()
           sqlDA.Dispose()
           sqlConn.Close()
           sqlConn = Nothing
           Return False
           Exit Function
       End Try

 

Have also tried 1.2.3.4/ServerName

 

This isn't intranet, this is public

[Robby]

Have you opened the Client Utility Tool | SQL Server | Program Files | then ADD a new location ?

[Robby]

Also, once you do so, open Enterprise Manager or Query Analyzer and attempt to connect.

[TheWizardofInt]

Is this in VS.Net?

[Robby]

Have you opened the Client Utility Tool | SQL Server | Program Files | then ADD a new location ?

[TheWizardofInt]

Tried it - didn't work. As well, I have to be able to set the IP dynamically (at run time, not design time)

[Robby]

Leave VS alone for a moment. Are you able to establish a connection using the "Client Utility Tool" and "Enterprise Manager"?

[TheWizardofInt]

No, I cant. Tried with windows and sql authentication

[Robby]

What steps did you take?

[TheWizardofInt]

I tried to create a new connection using the IP address of the remote server and SQL authentication. This failed, so I tried to use windows authentication, and this also failed.

 

It comes back the same for each: SQL server doesn't exist or incorrect login or password.

 

There is an article on MS that says that this can only be done by creating a DSN, which is what I will probably try next.

[Robby]

So I can assume that you have yet to try the Client Utility Tool ?

[georgepatotk]

TheWizardofInt, might or might not be the problem of network setting. Is your connection to the SQL server been set in the firewall? DMZ or NAT must be set to trasfer SQL packets to you internal SQL server.

 

I'd experienced the same error for my previous project. And NAT had solved my problem.

[TheWizardofInt]

I was just thinking that yesterday. When you say NAT, what do you mean?

 

I was thinking that I would have to do the connection through a PORT 21 or something like that, and configure the port for two-way transfer

[georgepatotk]

If you are talking about SQL server, the port would be 1433 and 1433.

 

NAT = Network Address Translators. It depends on your firewall whether it supports NAT or DMZ. Some of the firewall support both. But for security purposes, I would suggest NAT rather than DMZ.

 

For what I had done, I just set the firewall to pass all the reeived packets with port 1433 to Internal server(192.168.0.5).

 

And ask the firewall to accept all the packets with 1434 to be sent out.

 

Kindly inform me whather this solve your problem. Hope that I could help