How to connect to MS SQL Server database from your Android App? – complete steps

This video shows the detailed steps to install and configure the MS SQL Server and Microsoft SQL Server Management Studio on your machine.

It shows the steps of how to boot up the (start) SQL services and then enable it (in SQL Server Configuration) to listen on a tcp/ip port. It uses local ip address and default port to connect to the SQL Server.

Later it shows how to use MS SQL Server Management Studio (SSMS) to create a new login ID (and password). It uses the new credentials to create new database and table and insert values in it.

Then it switches to Android Studio to show in simple steps of how to develop an App to interact with the MS SQL Server and database. It implements the required driver in the App’s gradle file of the project. It defines the required INTERNET permission in the manifest file. And then in java code it creates a Connection which is used for quering to the database.

For any questions, suggestions or appreciations, please reach out to us at:
https://programmerworld.co/contact/ or write to us at: programmerworld1990@gmail.com

Java code:

package com.example.mymssqlserverdatabaseconnection;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;

import android.Manifest;
import android.content.pm.PackageManager;
import android.os.Bundle;
import android.os.StrictMode;
import android.view.View;
import android.widget.TextView;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MainActivity extends AppCompatActivity {

    private TextView textView;

    private static String ip = "192.168.1.101";
    private static String port = "1433";
    private static String Classes = "net.sourceforge.jtds.jdbc.Driver";
    private static String database = "testDatabase";
    private static String username = "test";
    private static String password = "test";
    private static String url = "jdbc:jtds:sqlserver://"+ip+":"+port+"/"+database;

    private Connection connection = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        ActivityCompat.requestPermissions(this,new String[]{Manifest.permission.INTERNET}, PackageManager.PERMISSION_GRANTED);
        textView = findViewById(R.id.textView);

        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        try {
            Class.forName(Classes);
            connection = DriverManager.getConnection(url, username,password);
            textView.setText("SUCCESS");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            textView.setText("ERROR");
        } catch (SQLException e) {
            e.printStackTrace();
            textView.setText("FAILURE");
        }
    }

    public void sqlButton(View view){
        if (connection!=null){
            Statement statement = null;
            try {
                statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("Select * from TEST_TABLE;");
                while (resultSet.next()){
                    textView.setText(resultSet.getString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        else {
            textView.setText("Connection is null");
        }
    }
}

Manifest file:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.mymssqlserverdatabaseconnection">

    <uses-permission android:name="android.permission.INTERNET"/>

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Gradle File

apply plugin: 'com.android.application'

android {
    compileSdkVersion 29
    buildToolsVersion "29.0.3"

    defaultConfig {
        applicationId "com.example.mymssqlserverdatabaseconnection"
        minSdkVersion 23
        targetSdkVersion 29
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }

}

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])

    implementation 'androidx.appcompat:appcompat:1.1.0'
    implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
    testImplementation 'junit:junit:4.12'
    androidTestImplementation 'androidx.test.ext:junit:1.1.1'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'

    implementation 'net.sourceforge.jtds:jtds:1.3.1'
}

Layout XML file:

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="167dp"
        android:layout_marginTop="109dp"
        android:text="Hello World!"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/button" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="150dp"
        android:layout_marginTop="115dp"
        android:onClick="sqlButton"
        android:text="@string/sql"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

</androidx.constraintlayout.widget.ConstraintLayout>

Question and Answer (Q&A) Section:

Qs: How to make connection if using sql express?


Ans: 
Above source code can be used to connect to sql express and it can work fine.
But can't be used directly using internal ip.
Use public ip on router then I use ip forwarding to ip sql server local and port 1433.

Comments (59)

  1. Would there be a different implementation for Mac users? I was trying to follow along the tutorial but at the end I was unsuccessful connecting to my database.

    Reply

    1. No, for Mac also the concept will remain same. Though the driver may not work in the Emulator if Mac OS is blocking it (which is very unlikely from my understanding). To test this you can try to run it directly on a real phone.

      Good Luck
      Programmer World

      –

      Reply

  2. I have MSSQL SERVER Express installed on my computer and SQL Server Agent does not work. Is it mandatory that SQL server agent should run.
    Thank you in advance

    Reply

    1. Yes, it will be mandatory for the SQL server to be running. Otherwise when the client (Android App in this case) tries to communicate with the server, it will not be able to communicate as the server is not running. Also, please ensure that the server is able to listen on the IP (address) and port number to which the Android App is communicating to.

      I hope the above helps in answering your question.

      Cheers
      Programmer World

      –

      Reply

  3. Great!!!!!. Right now I received the data from MS SQL server. Thank You.
    Only one question: I receive always exception on textView.setText. This is not critical, because I need to receive and send data to SQL but how can I escape this error?
    Thank You for the great help.

    Reply

  4. Hi,
    I’m trying to connect to a database hosted in azure using your code. I switch your “String url” with the one provided by azure and always keep sending me Failure when running the program.
    Any suggestions I’m kinda lost.

    Thank You,

    Reply

    2. Maybe I can help, I also have similar project (using Microsot azure database).

      The url should be changed into this:
      String url = “jdbc:jtds:sqlserver://.database.windows.net:1433/”;
      The rest just the same as this tutorial.
      It worked for me. If it doesn’t work, try changing the port number (I’m not sure what the default number, but 1433 worked fine for me).

      Reply

      1. String url = “jdbc:jtds:sqlserver://server_name.database.windows.net:1433/database_name”;

  7. I created the database on internet server and program it worked
    but when try on database local network does not work i think the problem in SQL Server Management Studio
    SQL Server (MSSQLSERVER) : Running
    SQL Server Agent (MSSQLSERVER) : Running
    SQL Server Browser : Running
    port sql server 1433 : open

    Reply

    1. I think you can connect to any MS SQL database available or accessible on the network. Just make sure to put the connection attributes to the server correctly in the below line of codes:

      private static String ip = “192.168.1.101”;
      private static String port = “1433”;
      private static String Classes = “net.sourceforge.jtds.jdbc.Driver”;
      private static String database = “testDatabase”;
      private static String username = “test”;
      private static String password = “test”;
      private static String url = “jdbc:jtds:sqlserver://”+ip+”:”+port+”/”+database;

      Hope it helps.

      Cheers
      Programmer World

      –

      Reply

      2. Check if your database is accessible on the network (without trying from Android App). If it is accessible over network then it should work from the App also.

        My guess is that the mentioned error is because the database is not accessible on the network.

        Cheers
        Programmer World

        –

  11. Hello, I have a small problem. At this point:
    try {
    Class.forName(Classes);
    connection = DriverManager.getConnection(url, username,password);
    textView.setText(“SUCCESS”);
    textView.setVisibility(View.INVISIBLE);
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    textView.setText(“ERROR”);
    textView.setVisibility(View.VISIBLE);
    } catch (SQLException e) {
    e.printStackTrace();
    textView.setText(“FAILURE”);
    textView.setVisibility(View.VISIBLE);
    }
    I get in the TextView Success.
    But when I try to execute SELECT, nothing happens. The data is not sent to or retrieved from the database. I did everything according to your video, but it still doesn’t work. Please give me some advice.

    Reply

  13. It is working smoothly if app and Sql server on the same network but not working if MS SQL server and phone are not on the same network?

    Reply

    1. It should work even if the App and SQL server are running on different networks. Of course the App should be able to access the IP of the server (should be able to access the database server). The speed/performance will also depend on the latency between the 2 networks. That is the ping time. If the ping time is high then there may be a bit of performance issue in working with 2 different networks. Otherwise it will be fine.

      Cheers
      Programmer World

      –

      Reply

  14. Sir,

    I tried your program, but failed to get the result despite several trials. The error message is:
    Session ‘app’: Installation did not succeed.
    The application could not be installed: SHELL_UNRESPONSIVE

    Could you kindly help me. Another query: In the sql string you mentioned only ip, port and database, not the server name. Is it not necessary to mention the server name?

    Best regards

    Fysal Usman

    Reply

    1. App installation fails when there is a mismatch in min Sdk version of your App and the device’s Android version (like device’s version is older than App’s version). There could be some other similar issues, such as some configuration in your App development environment (Android Studio).

      To fix this, I will suggest that you first try to create just a dummy App and try to build and run on the device. If that succeeds then proceed with that Android Studio environment by implementing your code. If it fails then check the configuration. Most likely changing to an older version of SDK may help.

      For the other query, ip address or server name are interchangeable. So, if we use ip then server name is not required or vice versa. Both, server name and ip, are to connect to right machine over the network. So, anyone is fine to use.

      Hope above helps.

      Cheers
      Programmer World

      –

      Reply

  16. Sir,

    The above mentioned problem is somehow resolved (I don’t know how, but I reinstalled Android Studio). Now my problem is the connection failure. I get the 1st message “Error” indicating ‘ClassNotFoundException’. The net.sourceforge.jtds.jdbc.Driver is not installed in my laptop. Do I have to install it first?

    Thanks a lot Sir for your valuable help and support.

    Fysal Usman

    Reply

    1. Check if the below implementation is included in the gradlle file:

      implementation ‘net.sourceforge.jtds:jtds:1.3.1’

      Refer to the source code on this webpage.

      Cheers
      Programmer World

      –

      Reply

      1. Sir,

        It is not included, but when I included the opening and ending single commas and the colon are highlighted red with comments unexpected “‘” and “no candidate found for method net”.

  17. Thank you very much Sir. It works now. There was a problem with SDK version as that mentioned in your gradle.build was not in my Android, so there was error, but I resolved it. I will ever remain grateful to you for your help and support.

    Reply

  18. Sir,

    Is it possible to create a combobox or textvox suggestion list, retrieving data from a table in the sql database, in this android app as we create in vb.net or c#?

    Reply

    1. Yes, it is easily possible. In fact there are ample of examples available on my website: https://programmerworld.co/android/

      Some of the quick examples which you can refer are:
      https://programmerworld.co/android/create-a-simple-business-app-using-sql-lite-database-in-android-studio-for-beginners/
      https://programmerworld.co/android/how-to-develop-an-android-app-for-your-retail-business-b2c-using-firebase-database-source-code/

      Hope above helps.

      Cheers
      Programmer World

      –

      Reply

    1. Dear Sir,

      My app is able to fetch data from the server lying in my laptop when the laptop and mobile are on the same network, but fails when they are on different networks.

      Kindly help to resolve this issue.

      Thanks a lot for all the help and assistance in making android apps.

      Fysal Usman

      Reply

      1. It is very obvious that for the app to be able to fetch the data from the database (server), it should be able to reach the server.

        So, when the phone (App) and laptop (database server) is on the same network it is able to connect to the server and fetch the data.

        But when they are on separate network then, of course, the App is not able to reach the server and hence, it will not be able to fetch the data.

        In simple words, the phone should be able to ping the database server IP. Below concept can be used to check the accessibility of the server from the App (just replace http://www.google.com address with the db server’s IP):
        https://programmerworld.co/android/how-to-check-internet-connection-from-your-android-app/

        Cheers
        Programmer World

        –

  20. Dear Sir,

    By same network, I meant the same wi-fi. When the laptop is on wi-fi and the phone is , for example, on its sim’s net, it does not work. When I am some 300 km away with my phone, it is not possible that phone and laptop are on the same wi-fi. in that case, how can I run my app.

    At the same time I will be, God willing, try to ping with your guidance given in the link given in your reply.

    Thanks a lot Sir.

    Fysal Usman

    Reply

    1. It doesn’t matter on the type of network. The only important point is that the server’s IP should be pingable/ reachable from the Phone’s App. Also, if the server is on wifi and phone is on mobile data then you will need to expose the server on a public IP which can be reached over the internet (outside of your LAN). If you are having a normal broadband connection (dynamic IP) then creating a public IP will be difficult from local PC. Best option will be to host the database on some public server (there are lots of hosting platform, just google).

      I hope above helps.

      Cheers
      Programmer World

      –

      Reply

  21. Dear Sir,

    Thanks a lot for your reply. Could you kindly guide me how to host the database on public server with an example.

    Fysal Usman

    Reply

    1. As of now I do not have an example to show the steps to create or host a public server. I will create a page on this topic if time permits. However, just to note, creation of the database on the public servers will depend on the specific portal of the host. Anyway I will try to create a generic page on this topic.

      Cheers
      Programmer World

      –

      Reply

  22. Dear Sir,

    Further to my above post, I am giving below the code that helps reach the server. Kindly advise what other codes are necessary. When I searched the web, I understood there are public DNS like of Google 8.8.8.8 & 8.8.4.4. How to add this in my code.

    The code used in my program:
    private static String ip = “192.168.18.18”;
    private static String port = “1433”;
    private static String Classes = “net.sourceforge.jtds.jdbc.Driver”;
    private static String database = “SelectionShoes”;
    private static String username = “sa”;
    private static String password = “12345678”;
    private static String url = “jdbc:jtds:sqlserver://” + ip + “:” + port + “/” + database;
    private Connection connection = null;

    I will be obliged to receive your valuable reply.

    Fysal Usman

    Reply

    1. DNS is not required. DNS servers are only to resolve the names into corresponding IP addresses. So, in your code you just need to enter the public IP (or server name) for your database server.

      Cheers
      Programmer World

      –

      Reply

  23. Hello,

    Thanks for your efforts.
    How can I keep my MS-SQL connection stable when I published the App on Google App Store. There are a lot of restrictions and limitations. Do you prefer to use Firebase instead. What about if I want to connect with Godaddy or any external host. Kindly give me your advice.

    Thanks.

    Reply

    1. Firebase or any cloud based database will always be preferable as google can validate the connection and thus, will allow it on play store.

      For any private database connection google would have restrictions. But other externally hosted servers should be allowed by the google’s app store.

      Cheers
      Programmer World

      –

      Reply

    1. As long as the connection between the server and the App is secure, it will be safe to access the database using the server name or ip address through the jtds library.

      Please share if you have different opinion.

      Cheers
      Programmer World

      –

      Reply

  25. Completely useless. It’s not realistic, meaning if you export your app to your phone and install it, it won’t work anymore.

    Reply

    1. It will work as long as the database server and phone is on the same network. Please ensure that the database IP is reachable from the phone.

      Cheers
      Programmer World

      –

      Reply

  26. Hi, i currently have MYSQL Workbench in my computer. Does this work for MYSQL Workbench as well? Or do I need to change some stuff?

    Reply

    1. This is for MS SQL and would not work for MySQL. For MySQL you will have to include the MySQL specific dependencies/ drivers and import them in the Java code. Unfortunately, as of now I do not have a demo page to show this.

      Cheers
      Programmer World

      –

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

wpChatIcon
    wpChatIcon