Microsoft Azure SQL Server connection to access SQL Database

#1

Hi All,

I’m back again with more queries :stuck_out_tongue_closed_eyes:.

I am having trouble connecting Wappler to the SQL databases that I have setup in Azure. First things first, I have the FTP correctly set up as I am able to deploy my code through Wappler to my Azure Web App. I am able to connect to the Azure Sql Server using the Microsoft Azure Data Studio application. The connection settings that provide correct connection for the Data Studio application are as follows:

Connection type: Microsoft Sql Server
Server: XXX.databse.windows.net
Autentication Type: SQL Login
Username: YYY
password: ZZZ

I am now trying to setup a server connect database connection from inside Wappler. I have tried using multiple database types in the database connection form in Wappler also to no avail. Am I correct in assuming that I should be using the SQL Server option? Anyway, if I try to connect using the Sql Server option, I get a very long error message. I am not able to copy the error message or expand the error window (perhaps this could be a valid functionality request), but I have attached a few screenshots of the initial lines of the error message. Looks like an authentication error perhaps?

EDIT: Is this an Azure error message or a Wappler error message does anyone know? Would I be correct in imagining that there is actually an error in the error message too, I feel like all this code should not be included in the error message?

I’m not sure if this helps or not but the connection strings that are listed for this Azure database are as follows:

ODBC
Driver={ODBC Driver 13 for SQL Server};Server=tcp:XXX.database.windows.net,1433;Database=Test;Uid=YYY@YYY;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

PHP
// PHP Data Objects(PDO) Sample Code:
try {
$conn = new PDO(“sqlsrv:server = tcp:XXX.database.windows.net,1433; Database = Test”, “YYY”, “{your_password_here}”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
print(“Error connecting to SQL Server.”);
die(print_r($e));
}

// SQL Server Extension Sample Code:
$connectionInfo = array(“UID” => “YYY@YYY”, “pwd” => “{your_password_here}”, “Database” => “Test”, “LoginTimeout” => 30, “Encrypt” => 1, “TrustServerCertificate” => 0);
$serverName = “tcp:XXX.database.windows.net,1433”;
$conn = sqlsrv_connect($serverName, $connectionInfo);

Any help that anyone could offer would be really appreciated. I am really hoping to get this sorted. Perhaps this could eventually result in a full Wappler and Azure tutorial. Thanks in advance guys.

Perhaps I can use the connection strings above to manually create a connection file so that the rest of the Wappler functionality works?

EDIT: I know it’s a lot to ask, but if anyone who was willing to help trouble shoot this, it’s free to set up an Azure account, or perhaps we could organise a Skype call or something and we could trouble shoot directly on my Azure account?

#2

If I have the server model setup as PHP on a project and create an Sql Server database connection, what type of connection does Wappler try to use? Is it PHP, ODBC, JDBC or ADO.NET?

Similarly if I set the server model to ASP.NET which of the above connection types does Wappler use? Would it be possible to use the connection strings I included above to manually create connection files to my database so that I could use the rest of the Wappler server connect functionality? Any guidance or help someone can give me would be really appreciated. Can offer pints or coffees to anyone that can help that ever finds themselves in Dublin. :slight_smile:

#3

When running PHP - Wappler uses PDO for all database access.

So when connecting to Azure - always make sure the PDO SQl Server Driver is installed on your web server. See

https://docs.microsoft.com/en-us/sql/connect/php/loading-the-php-sql-driver?view=sql-server-2017

For ASP and ASP.NET Wappler uses ADO

#4

Thanks for the reply George, apologies @sbecks if I am hijacking your thread, but I believe we have the exact same issue.

It seems that in my case these drivers are already installed, see below screenshot from SSH in Azure App service. Any other ideas, of what might be the issue?

#5

Well try to run a php file with phpinfo inside and see the report

#6

Thanks for the help George, new to web dev, it’s a steep learning curve. phpinfo gives the following related to PDO.

PDO

PDO support => enabled
PDO drivers => sqlite, sqlsrv, mysql, odbc, pgsql

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

Directive => Local Value => Master Value
pdo_mysql.default_socket => no value => no value

PDO_ODBC

PDO Driver for ODBC (unixODBC) => enabled
ODBC Connection Pooling => Enabled, strict matching

pdo_pgsql

PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 9.6.11

pdo_sqlite

PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.24.0

pdo_sqlsrv

pdo_sqlsrv support => enabled
ExtensionVer => 5.6.1

Directive => Local Value => Master Value
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0

#7

And you still getting the “sign in to your account” error as at first when doing dB connection test?

If so send me your credentials in private message so I can investigate more in detail. If you don’t mind of course

#8

Thanks George,

I am not getting the sign in errors now, just the driver ones:

I created a new Azure App service and started from scratch.

#9

And you installed the SQL server drivers? The show up as PDO drivers when you run phpinfo() ?

#10

Do you also have the Microsift ODBC driver 17 installed, it is required. See https://github.com/microsoft/msphpsql#prerequisites

Also you could check if it works with the PDO sample code:

// PHP Data Objects(PDO) Sample Code:
try {
  $conn = new PDO(“sqlsrv:server = tcp:XXX.database.windows.net,1433; Database = Test”, “YYY”, “{your_password_here}”);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  print(“Error connecting to SQL Server.”);
  die(print_r($e));
}
#11

@Patrick @George

I tried this and it is indeed giving me an error that the ODBC driver is not installed. After a little googling it looks like this driver cannot be installed on the Azure App service. I think I will have to go down the Virtual Machine root instead of Azure App service.

I am now in way over my skill and experience level. I can spin up a VM in Azure. Has anyone deployed an app to a VM using Wappler before? Any suggestions on stacks, web servers etc. Should I try go down the LAMP or NGINX route?

#12

This is weird - so you have the Azure App Services and want to connect to Microsoft its own SQL Server but you can’t because they don’t allow the required ODBC drivers to be installed? What a service … not allowing to connect to their own stuff.

Why do you actually choose for Azure? You will be much better off getting a cheap PHP hosting with mySQL as database - this at least always works.

#13

So what I am actually trying to do is build a web application that ingests data from iot devices. I chose Azure because they have a pretty straightforward setup for taking data from the devices and storing it in an Sql Server, and I was also looking at integrating some the powerBI dashboard software.

I am currently running a web app on a cheap php hosting package, and tried to move entirely over to Azure just to keep everything in one place, and I liked the idea of having a cloud based solution for scaleability reasons.

Thanks everyone for the help. Looking forward to seeing a Wappler tutorial on setting up on a virtual machine in the future.

#14

Did you try to use ASP.NET instead of PHP?

#15

Hey Marcel,

yeah I tried using the ASP.NET as a server model in Wappler but to no avail.