Error when connecting to MSSQL Server with Google Apps Script via JDBC

Question

I'm trying to connect to Microsoft SQL Server using Google Apps Script. I'm using SQL Server 2008 R2 and I am using one of the suggested scripts that's supposed to read data and put it into a Spreadsheet: https://developers.google.com/apps-script/jdbc#reading_from_a_database

The error message is:

Failed to establish a database connection. Check connection string, username, and password

Username and password are OK, the user is DBowner. The port is also correct, I tried to connect to the server via Telnet using: o IP-address 1433 and it works.

Here's the code:

function foo() {
    var conn = Jdbc.getConnection("jdbc:sqlserver://IP-adress:1433/DBName","user","password");
    var stmt = conn.createStatement();
    stmt.setMaxRows(100);
    var start = new Date();
    var rs = stmt.executeQuery("select * from person");
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var cell = doc.getRange('a1');
    var row = 0;
    while (rs.next()) {
        for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
            cell.offset(row, col).setValue(rs.getString(col + 1));
        }
        row++;
    }
    rs.close();
    stmt.close();
    conn.close();
    var end = new Date();
    Logger.log("time took: " + (end.getTime() - start.getTime()));
}

Do you have any idea of what can be wrong? Do I have to make some configuration on my Server? Or in the database? The instructions mentioned above says to ensure that Google's Ip-addresses can reach the database. But instead of listing all of Google's IP-addresses I granted access to all on that port. I also enabled TCP/IP Protocol in SQL Server Configuration Manager. And I granted "Remote connections" to the Server in MSSMS. Any other idea, please?

Answer 1

Well, I found the answer here: Google Apps Scripts/JDBC/MySQL

Obviously, the connection string has to look like this:

var conn = Jdbc.getConnection("jdbc:sqlserver://IP-address:1433;" + "databaseName=DBName;user=username;password=password;");

I don't understand why connection string differs from the one in Google Documentation but this one works for me...

Answer 2

The Issue is in the Connection string.

It should be like so

          address = '%YOUR SQL HOSTNAME%';
          user = '%YOUR USE%';
          userPwd = '%YOUR PW%';
          dbUrl = 'jdbc:sqlserver://' + address + ':1433;databaseName=' + queryDb;
          var conn = Jdbc.getConnection(dbUrl, user, userPwd);

I have an entire tool on GitHub where you are able to connect to MySQL and SQL Servers. I you will be able to use it to help you. I will be constantly updating with more features overtime! You can find it here.

GOOGLE SPREADSHEET JDBC CONNECTOR TOOL