Recent

Author Topic: Azure sql database will not connect  (Read 5201 times)

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Azure sql database will not connect
« on: April 12, 2024, 06:42:04 pm »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #1 on: April 12, 2024, 07:32:35 pm »
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
Best regards / Pozdrawiam
paweld

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #2 on: April 12, 2024, 07:45:00 pm »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #3 on: April 12, 2024, 07:58:50 pm »
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!
Best regards / Pozdrawiam
paweld

paweld

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #4 on: April 13, 2024, 07:33:58 am »
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.  
Best regards / Pozdrawiam
paweld

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #5 on: April 15, 2024, 09:04:08 pm »
Thanks, that works.  Now I just have to remember how I did the rest of the program after not touching it for a year  :D

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #6 on: April 16, 2024, 04:04:56 pm »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #7 on: April 17, 2024, 07:41:17 am »
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.
Best regards / Pozdrawiam
paweld

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #8 on: April 17, 2024, 06:21:08 pm »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #9 on: April 17, 2024, 07:05:00 pm »
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
Best regards / Pozdrawiam
paweld

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #10 on: April 17, 2024, 07:15:27 pm »
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

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #11 on: April 17, 2024, 07:56:41 pm »
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

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #12 on: April 17, 2024, 08:00:13 pm »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Azure sql database will not connect
« Reply #13 on: April 17, 2024, 08:06:26 pm »
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
Best regards / Pozdrawiam
paweld

wpflum

  • Sr. Member
  • ****
  • Posts: 287
Re: Azure sql database will not connect
« Reply #14 on: April 17, 2024, 08:14:05 pm »
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.

 

TinyPortal © 2005-2018