Xojo and Azure SQL

Hi all,
i tried to Play around with Azure SQL and Xojo… Can anyone give me a tip how to connect to a Microsoft Azure SQL database with XOJO without ODBC ?
For example i have a table “Adressen” in test database named “XOJO” running on an instance on Azure SQL with the servername “xojo.database.windows.net” ?
With ODBC… no Problem… But how to use the MSSQL (plugin) stuff directly from Xojo ?
With the ODBC ConnectionString it Looks something like this :
Driver={SQL Server Native Client 11.0};Server=tcp:xojo.database.windows.net,1433;Database=Xojo;Uid=hbach@xojo;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
Any idea ??
I want to try and test how stable and fast XOJO is when it comes to Desktop Mac / Windows AND Webapplications (Xojo Cloud and/or 1701Software) with the AzureSQL combination.
Thx for your help !!!
Henning

If the application is running on Windows, you can connect using ADODB and the native client.

Here is a link to some relevant information

I use ADODB all the time with MS SQL Servers on my network. Works quite well with Xojo.

Hi Louis…
thx for answering…
I use a Mac for development and the backend for the Webapplication will be maybe Xojo Cloud or 1701Software.
Do you have any example (in Xojo) (i’m a Xojo Newbie) on how to do it with a Mac Environment ?
How do you use it with ADODB on Windows… can you provide a small source code snippet with my demo informations/Connections in the first post ??
THX !!!
Henning

if you are going to be using a linux host (or a mac) you cant use ADODB, its windows only.

do yourself a favour and get the MBS SQL plugin and use freetds

I did a presentation back in 2012 for the London UserGroup on Cloud databases - the SQL plugin at that time didn’t support SQL Azure. Obviously that was a LONG time ago (though it seems like yesterday)

Trying to remember why and it escapes me - think it may have been the “.” in the hostname (xojo.database.windows.net). My memory may be failing though

Things may have changed of course in that time, I no longer use SQL Azure.

I use the MBS SQL plugin as a matter of course.

Hi all…
Any idea if Azure works with MBS SQLPlugin ?
Greetings
Henning

Yes,

I used MBS SQL to do that presentation connecting to SQL Azure and also other projects. Just bear in mind that I last tested the native plugins quite a while ago so it may have changed…

Well, try it :slight_smile:

I do this but I use Xojo to connect to a PHP page that does the actual connection to the database which is also hosted on an Azure website (MS Azure website hosting is free). This is really the most secure way to do this because it does not expose your database to the outside world and you will not have your database connection strings stored in your application. You can then use standard HTTPS protocol to talk to the PHP page and interact with the database. It IS more work, but the correct way to do this.

I was originally going to connect doing exactly what you are planning but after doing tons and tons of research and reviewing security measures the above method is the correct way to do it. I did not know PHP at the time, but after working with it a bit it was pretty easy to pick up. Additionally, there is a massive amount of support on MS Azure with regard to interfacing PHP and Azure SQL.

Good luck, Azure is a good cloud platform.

Hi Joseph…
hmmmmm… i know some PHP on the server side (f.e. to write a Webservice) but i actually don’t have any idea about your solution. Can you explain what you mean with “connect to a PHP page that does the actual connection” ?
Thx !!!
Henning

[quote=266178:@Henning Bach]Hi Joseph…
hmmmmm… i know some PHP on the server side (f.e. to write a Webservice) but i actually don’t have any idea about your solution. Can you explain what you mean with “connect to a PHP page that does the actual connection” ?
Thx !!!
Henning[/quote]

Henning,

Instead of having your application interface with your DB on Azure you would actually create a PHP web service that does that for you. This way, your DB can be secure and limit all connections from the outside world, only allowing a connection from the PHP service. You would create a website in Azure and then develop the PHP scripts, procedures to connect to the DB. For example, this page tells you how to connect to the database with PHP and provides mountains of information on how to do this, it is actually better than the MS documentation:

http://www.w3schools.com/PHP/php_mysql_connect.asp

Here is the official link from MS Azure on connections:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-php-simple/

These page discusses the process and what is available to access the database:

http://social.technet.microsoft.com/wiki/contents/articles/1258.accessing-sql-server-databases-from-php.aspx

You would then use standard HTTPS protocol from your Xojo application to requests data from the PHP page that may return records or add records using HTTPS POST commands or JSON. The connection string is actually stored in the PHP page (secure). But login information for individual users would likely be stored in the database. This way, you can easily manage user login by way of modifying the data in the database.

IF you decide to connect directly to the database through Xojo you will have to open your database to the outside world because you do not necessarily know the IP addresses of the potential users. This is dangerous; imagine having unknown users sending thousands of login requests to your DB (which will happen). You will then need to somehow store the DB connection string in your app, but that can easily be determined, even if obfuscated.