How to access SQL Server database in network? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to access SQL Server database in network?

Author

Topic

Ventimiglia
Starting Member

6 Posts

Posted - 2007-07-31 : 03:49:02

Hello!

I have a Java application that gains access to a SQL database using:

connection="jdbc:jtds:sqlserver://localhost:1433/databasename;user="+user+";password="+password;

//Load and register SQL Server driver
Class.forName("net.sourceforge.jtds.jdbc.Driver");

//Establish the connection
conn=DriverManager.getConnection(temp);

When I distribute this application throughout the local network, what would the jdbc url be for the application to find the SQL server?

Should I replace "localhost:1433" with something else?

The instance of SQL server is called SQLExpress.

I have my computer name, computer domain, ip address etc.

Is the port 1433 still the same?

How could the SQL database be located throughout the network?

Any advice appreciated.

Ventimiglia
Starting Member

6 Posts

Posted - 2007-08-01 : 02:37:57

..or do tou think creating an applet of the Java application is the easier way, accessing the database from a tomcat server?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-01 : 11:25:56

Replace localhost with sql instance name, and have to check port number on the instance you like to connect to.

Ventimiglia
Starting Member

6 Posts

Posted - 2007-08-02 : 03:40:30

Thanks for your reply.

With my computer name being SETPCA453601J and SQL Server instance = SQLEXPRESS I tried:

jdbc:jtds:sqlserver://SETPCA453601J:1433/Screws;instance=SQLEXPRESS;user="+user+";password="+password;

Unfortunately I got the message;
java.sql.SQLException: Unable to get information from SQL Server: SETPCA453601J.
java.sql.SQLException: Unable to get information from SQL Server: SETPCA453601J.
at net.sourceforge.jtds.jdbc.MSSqlServerInfo.<init>(MSSqlServerInfo.java:91)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:263)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)

I know localhost should be replaced with <servername> or <host>.
I've also seen approaches like
fastnet.dukece.com:1433
or
10.0.0.16:1433

Stupid question, really, but how do one figure out the server name?
(I'm in a company intranet and would like for others to be able to connect to my database)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-02 : 22:25:26

Your sql instance name should be SETPCA453601J\SQLEXPRESS, and ensure port number is 1433. Because named instance uses dynamic port number by default. You can use ip address instead of instance name as well.

Ventimiglia
Starting Member

6 Posts

Posted - 2007-08-03 : 06:11:59

>> Your sql instance name should be SETPCA453601J\SQLEXPRESS

Using this results in:

java.sql.SQLException: Network error IOException: Connection refused: connect
java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:372)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)
at Database.openConnection(Database.java:53)
at MainFrame.<init>(MainFrame.java:50)
at MainFrame$67.run(MainFrame.java:4271)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:519)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:289)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:250)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:297)
... 15 more
java.lang.NullPointerException
at Database.closeStatement(Database.java:91)

===========

Using a connection with only localhost:1433 works fine, however.

I read here
[url]http://www.webafrica.co.za/kb/sql/mssql_connect_remotely.html[/url]

that

"Problem/Summary
I am unable to connect to my SQL server from a remote location.

Cause
The default SQL port (1433) is blocked.Many ISPs and network providers block by default port 1433 for security measures due to SQL worms and hacker scans.

Resolution
Please use port 1444 to connect.

To accomplish this:

1. Open SQL Client ---> Start -> Run -> c:\windows\system32\cliconfg.exe
2. Click "Alias" Tab
3. Click the ADD button,
4. Under Server Alias type, sql.domain.com, substituting domain.com for your domain name.
5. Under "Network Libraries" on the left, please select TCP/IP.
6. Under Server Name type, sql.domain.com, substituting domain.com for your domain name.
7. Uncheck "Dynamically determine port"
8. Under Port Number, type 1444"

Any comments on whether to use port 1433 or any other?

Any ideas why SQL Server connects with
localhost:1433
but not
<computername>/<Serverinstance>:1433?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-03 : 10:30:15

Are you connect from remote machine? If so, ensure you enabled remote connection in sql server.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-08-03 : 14:46:14

Ventimiglia,

[quote user="Ventimiglia"]
Using a connection with only localhost:1433 works fine, however.
[/quote]

Apparently, you don't understand what localhost means. I'll try and explain.

Localhost is an alias for your own computer's network card (a loopback, with IP address 127.0.0.1). It points directly to the machine your application/webbrowser/whatever is running on. In other words, if you're sitting at the keyboard of a machine named "MyMachine", localhost points to MyMachine/127.0.0.1; however, if you're on "KensMachine", it points to KensMachine/127.0.0.1.
See why it won't work when you use it across the network? You're trying to connect to machine SETPCA453601J, but instead are connecting to your own machine.

You need to figure out the correct DNS name or IP address for the machine to which you need to connect. You can do this a couple of ways:

1. If it's part of a DNS, simply use PING SETPCA453601J

2. Go to the server and open a COMMAND window (using Start|Run, typing "CMD" without the quotes, and hitting Enter, since it's a Windows machine). Type "ipconfig /all" at the prompt (again without the quotes) and hit Enter. You'll see the server's IP address there in the listing.

3. Talk to your network administrator and ask for the correct machine name or IP address for use with that server from your application.

Note that #2 won't work if you're trying to access the machine through a router that's using NAT, because the internal IP probably isn't the same as the externally visible one.

allenj
Starting Member

1 Post

Posted - 2012-08-15 : 13:12:52

I had fits trying to connect via JTDS JDBC to SQL Server 2008 R2 on Windows 2003 server. It was all about the IP.
Fail: <real localhost IP>
Fail: localhost
Pass: <IP to another server MSSS>
Pass: 127.0.0.1
Full URL: jdbc:jtds:sqlserver://127.0.0.1;databaseName=WFM_DB;instance=STD
I have no clue why it behaves this way, but I thought it might help someone else.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources