Azure sql database will not connect

[wpflum]

I'm trying to use an Azure SQL database and not matter what i cannot get my program to connect to it.  I've tried using different connectors and the connection string that Azure shows me in the Portal to connect but keep getting an error 20002.  Nothing I search for seems to show me a package that will work. Right now just adding a connector to a form and trying to enable it is as far as I get.

 Any direction toward a solution would be appreciated.

[paweld]

I use SDAC components from Devart:  https://www.devart.com/sdac/
They are paid but work very well with MSSQL. They also connect to Azure SQL without a problem - there is a trial version available on the site, so you can check it out for yourself.
But I also think you should connect to Azure SQL using ZEOS components - I'll try to check it tomorrow and get back with an update

[wpflum]

I tried to install the trial version but can't figure out how, when I try adding the package it tells me it can not find MTSCall.pas.  I'll look into the ZEOS option.

[paweld]

The trial version works only on lazarus 3.0 with fpc 3.2.2.
You download the archive, unzip it so that the path has no spaces or characters other than ASCII, then in lazarus click the "Package > Open package file" menu and select the file "sdac\packages\dclsdac10.lpk" and click "Install" - important: do not compile the package!

[paweld]

Ok, I checked ZEOS and it also works without problems. I use ZEOS 8:

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   server, db, login, pass: String;
4. begin
5.   server := 'myAzureServerName.database.windows.net';
6.   db := 'myDatabaseName';
7.   login := 'myLogin';
8.   pass := 'myPassword';
9.   //zeos
10.   ZConnection1.Protocol := 'OleDB';
11.   ZConnection1.HostName := server;
12.   ZConnection1.User := login;
13.   ZConnection1.Password := pass;
14.   ZConnection1.Database := Format('Provider=MSOLEDBSQL;Data Source=%s;Initial Catalog=%s;Authentication=SqlPassword;User ID=%s;Password=%s;', [server, db, login, pass]);
15.   ZConnection1.Connect;
16. end;
17.  

[wpflum]

Thanks, that works.  Now I just have to remember how I did the rest of the program after not touching it for a year 

[wpflum]

Stupid question, I'd like to be able to use the IDE to play with the connection but nothing I do allows me to check the Connected box and not get an error.  Doing it in code seems to work but trying to add in the same options isn't working, so I'm missing something.  Any idea?

[paweld]

I suspect that the firewall is blocking the connection to the server. You need to add an exception on the firewall that excludes blocking Lazarus.

[wpflum]

The program is working but now I have another issue, I'm getting an error on another PC when I try and run the exe, 'Class not registered', it works on the machine it was compiled on.  I'm guessing it's related to the ZEOS library but what do i do to register it?

[paweld]

The "Microsoft OLE DB Driver for SQL Server" must be installed on the computer: https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

[wpflum]

Installed it and rebooted and still the same error.  I even installed the VC_redist.x64 just in case since it was mentioned on the Microsoft OLE DB Driver for SQL Server page

[wpflum]

This is weird,, I tried installing Lazarus and Zeos on the second computer and then compiled the program and still get the class error.   

[wpflum]

Just tried another PC and get the same error.  If I compile a simple test program with just a button that closes the form it runs on both PCs.

[paweld]

Did you install a driver that is compatible with the program, i.e. if you compiled a 32-bit program, the driver must also be 32-bit.
Also provide information about your operating system - I will try to check tomorrow

[wpflum]

Both PCs are Windows 11 64bit and the last one I tried was a Windows 10 64 bit, the 64 bit driver is what I installed.  I also narrowed it down to the Zeos library as if I add a TZConnection to the test program and have it try and connect to the database I get the same error as the big program.

I'm sure I'm doing something stupid.