Android Tutorial How to connect to SQL Server using jtds-1.3.1.jar

This subject keeps coming up so I thought I would do a tutorial and sample programs. One is B4A, the other is B4J. If you want code examples download the programs.

The B4J I created for testing as it should have fewer problems connection to an SQL DB from a Windows machine connected with a wire than a wireless tablet trying to connect. Or, if possible, run the Java program on the computer containing the instance of SQL. For example on my laptop I am able to connect without a problem using the Java program to connect to an SQL database on my laptop. The B4A REQUIRES an INBOUND RULE allowing the PORT I am using through the firewall.

I AM ABLE TO SUCCESSFULLY CONNECT TO AN SQL SERVER DATABASE USING THESE PROGRAMS FROM BOTH WINDOWS (B4J) AND ANDROID (B4A). THIS IS ALL I KNOW ON THE SUBJECT. IF YOU CANNOT GET IT TO WORK THEN MORE THAN LIKELY YOUR ISSUE IS FIREWALL/DOMAIN/SQL SERVER CONFIGURATION/SECURITY. I AM NOT AN EXPERT IN THESE AREAS. We have a number of customers using our program using this type of connection wirelessly on Android tablets. WHEN IT DOESN'T WORK I TELL OUR CUSTOMERS "CALL YOUR MIS/NETWORKING PEOPLE." They do and THEY have always fixed the problem. I have connected to SQL server versions 2008 - 2014 successfully. IF you want to connect to any other DB other than MS SQL Server then I cannot help you. If your SQL Server is on anything other than a Standard Windows/Windows server I cannot help you.

Things to know:
Android 7 and above required. 6 fails. I don't know why.
I have had this working on multiple tablets AND phones. Cheap ones and good ones.

You can download the driver file by Searching the net for jtds-1.3.1.jar or I found it
HERE.
Put the file in: C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries
or wherever your libs are if it is different. I was warned NEVER put it here but it is working.

You MUST SELECT 'SQL Server and Windows Authentication Mode'. I HAVE NEVER TRIED WINDOWS AUTHENTICATION! You must have SQL Server Authentication. If anyone ever gets it to work with Windows Authentication please let me know.
Open SSMS
Right-Click the Server
Select Properties
Select security page.
SELECT 'SQL Server and Windows Authentication Mode'
upload_2019-11-16_21-51-7.png


What you need:
The IP Address of the computer containing the instance of SQL Server.
The PORT address that SQL Server is listening on.
The NAME of a database on that SQL Server.
A VALID USER NAME/PASSWORD to connect to the DATABASE you have chosen.

IP Address:
Open the Windows menu ON THE SQL SERVER COMPUTER and type 'COMMAND' and hit enter.
This will open a command prompt.
Type 'ipconfig' this will get you a screen similar to this:
upload_2019-11-16_19-49-54.png


The IPV4 Address is what you are after. My laptop is currently wireless so that is what I have. More than likely your connection will be an Ethernet Adapter. IF YOU HAVE MORE THAN ONE CONNECTION I CAN'T HELP YOU. Try them all?


PORT Address:
Open SQL Server Management Studio.
Under Object Explorer Open 'Management'.
Expand 'SQL Server Logs'
Open 'Current'
Search for 'Any'
upload_2019-11-16_21-19-8.png


You are looking for the following line:
upload_2019-11-16_20-12-4.png

in this case the PORT we are after is 65218

IF YOU DO NOT FIND THIS ENTRY:
YOU MUST SET IT UP! THIS IS THE ONLY WAY I HAVE EVER GOTTEN THIS TO WORK!
DON'T FORGET YOU MUST RESTART THE SQL SERVER AFTER MAKING THESE CHANGES!

Open SQL Server Configuration Manager (or Computer Management and select Server Configuration Manager) .
Make sure TCP/IP is enabled FOR YOUR INSTANCE OF SQL. There may be more than on instance.

upload_2019-11-16_20-57-23.png

After enabling double click to open properties, select the IP Addresses Tab, and scroll down to IPAll (This is the LAST Entry)
and enter the PORT Number for TCP Dynamic Ports.

upload_2019-11-16_20-36-18.png


Normally this is 1433. I have more than one instance of SQL server so in this case I have a different Port.
RESTART THE SQL SERVER INSTANCE AFTER MAKING THESE CHANGES!

Once all of this is done the attached programs should work. If they do not then, as mentioned above it is more than likely some sort of network/security issue.

Tip:
Open the SQL server error log and look at what is failing. If you do not see a failed login then you aren't even getting to the server.

Hope this helps someone.

Kim
 

Attachments

  • SQLTestB4A.zip
    8.6 KB · Views: 639
  • SQLTestB4J.zip
    3.3 KB · Views: 410
Last edited:

joaquinortiz

Active Member
Licensed User
Thanks Kim, for sharing this tool. Do you think that it's secure to use this technique? I'm refering using a B4J, instead of connecting directly to SQL SErver?.

I know all about how to connect to SQL Server, because currently I develop in VB.NET with Framework and CompactFramework.

The thing that i want to express, is that I'm not sure or it doesn't convince me to have a server app to communicate with B4A.

What is your opinion or experience about this.? I'll glad to hear from you.
 

aeric

Expert
Licensed User
Thanks for sharing.
May I know is this meaning a direct connection to the database server and connect to any database?
I think JDBC2 is a better choice since it connects to a database only.
 

MrKim

Well-Known Member
Licensed User
I am aware there are security issues. All of our customers are small shops. Setting up and configuring and maintaining JDBC2 becomes an issue. What we do works for a single small location. I started with JDBC2 and no one liked it.
 
Last edited:
Top