So I've been fighting with this on/off for a few days and feel as if I've exhausted any relevant search terms. Let me begin by saying that I've done a lot of research into this to no avail; I've come to the conclusion that it's either something very abnormal (less likely) or it's some minuscule detail I've somehow overlooked. Let me also say right off that bat that yes, I am aware that connecting to the database directly from an app isn't ideal and that I should use a web server instead, but for my intent and purpose, this solution isn't an issue.
I know this post is super long, but I do appreciate everyone's time. If nothing else, at least it might help others who have recently started using JDBC and have started to encounter issues. The "what I've tried" section outlines the vast majority of things to try that you'll find online.
What I'm trying to do:
Using a JDBC driver, connect to my Azure-hosted SqlServer database via Android Studio.
The error I'm encountering:
W/System.err: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host vet-to-go.database.windows.net, port 1433 has failed. Error: "Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
What I've tried/verified (short list):
-Connection code happens within an activity class, however it's in a AsyncTask.
-uses-permission android:name="android.permission.INTERNET"
-Azure firewall is configured to allow my IP to connect.
-Windows firewall has rules in place to allow incoming & outgoing traffic on port 1433.
-From SQLServer Config Manager, I've enabled Named Pipes & TCP/IP. On the IP Addresses tab, I've enabled all and set TCP Port to 1433. Have tried both using & turning off TCP Dynamic Ports.
-Have tried with SQL Server Browser both running & stopped (another article mentioned doing so).
-Have tried using both jre7 & jre8 JDBC drivers.
-sourceCompatibility & targetCompatibility set at the respective 1_7 or 1_8 versions.
-compile files('libs/sqljdbc41.jar') [or 42 for jre8]
-More desperately and to ensure they weren't the problem, I've also failed to connect after turning Windows Firewall off entirely AND allowing any IP to connect via Azure Firewall (Start/end IP of 0.0.0.0 - 255.255.255.255)
-I can ping & telnet into (mydbname).database.windows.net 1433 (both DNS & actual IP)
Considering all of the above, I can only assume that the error falls somewhere within my code, but I absolutely cannot figure out where. In an effort to put a nail in the coffin, it should be mentioned that I was able to successfully query the DB & return a value in my project, but only in a static, non-activity class. Replicating the code almost exactly yielded no results when added to the actual activity class.
Here's the working code:
import java.sql.*;
public class DatabaseHelper {
public static void main(String[] args) throws Exception {
String custID = "1";
String connectionString = "jdbc:sqlserver://vet-to-go.database.windows.net:1433;database=Vet To Go DB;user=myLogin@vet-to-go;password=myPass;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
String queryString = "SELECT FirstName, LastName FROM Tbl_Customer WHERE CustomerID = " + custID;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection myConnection = DriverManager.getConnection(connectionString);
Statement myStatement = myConnection.createStatement();
ResultSet myResultSet = myStatement.executeQuery(queryString);
myResultSet.next();
String firstName = myResultSet.getString("FirstName");
String lastName = myResultSet.getString("LastName");
System.out.println(firstName);
System.out.println(lastName);
myStatement.close();
myConnection.close();
}
}
I know some of that code looks weird, it's only because I expected to incorporate it into another class. Here's my most recent attempt at doing so (as I mentioned, I've been at it for a while so I've tried a few different combinations)
import android.content.Context;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.TextView;
import java.sql.*;
public class LandingPage extends AppCompatActivity {
TextView txtFirst, txtLast;
String firstName, lastName, customerID;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_landing_page);
txtFirst = (TextView)findViewById(R.id.txtFirstName);
txtLast = (TextView)findViewById(R.id.txtLastName);
customerID = getIntent().getStringExtra("custID");
new myAsyncTask(LandingPage.this).execute();
txtFirst.setText(firstName);
txtLast.setText(lastName);
}
public class myAsyncTask extends AsyncTask<Void, Void, String> {
String connectionString = "jdbc:sqlserver://vet-to-go.database.windows.net:1433;database=Vet To Go DB;user=myLogin@vet-to-go;password=myPass;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
String queryString = "SELECT FirstName, LastName FROM Tbl_Customer WHERE CustomerID = " + customerID;
Context context;
public myAsyncTask(Context context) {
System.out.println("Async instantiated.");
this.context = context;
}
protected void onPreExecute() {
System.out.println("Pre-executing Async task.");
}
protected String doInBackground(Void... params) {
try {
System.out.println("Attempting to connect to DB...");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(connectionString);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(queryString);
while (rs.next()) {
System.out.println("Sorting through ResultSet...");
firstName = rs.getString("FirstName");
lastName = rs.getString("LastName");
}
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
return "Complete";
}
protected void onPostExecute(String result) {
if (result.equals("Complete")) {
System.out.println("Async task complete.");
}
}
}
}
After running the above code, I get the "The TCP/IP connection to the host vet-to-go.database.windows.net, port 1433 has failed." error at this line:
Connection con = DriverManager.getConnection(connectionString);
I don't think it's pertinent, but just in case it is, here's all that's currently in the MainActivity:
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
etID = (EditText)findViewById(R.id.etCustID);
btnSubmit = (Button)findViewById(R.id.btnSubmit);
btnSubmit.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
customerID = etID.getText().toString();
myIntent = new Intent(MainActivity.this, LandingPage.class);
myIntent.putExtra("custID", customerID);
startActivity(myIntent);
}
});
}
Android Studio 2.3
Build #AI-162.3764568, built on February 24, 2017
JRE: 1.8.0_112-release-b06 amd64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Gradle version 3.3
Target API 22: Android 5.1
Min Sdk V 21
Build tools v 25.0.0
