FireDac SQL Azure SQL_COPT_SS_ACCESS_TOKEN OAuth2

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,

J'essaie de me connecter à un instance managée de SQL Azure, ce que je parviens à faire en authentification
- SQL server,
- "Active Directory Interactive" (L'AADDS (Azure Active Directory) nous permet de nous authentifier sur le server SQL en MFA).

Pièce jointe 614970

Mon problème : je souhaite me connecter à l'aide d'un token, grace l'attribut de connexion SQL_COPT_SS_ACCESS_TOKEN.

Voici le code que j'utilise pour récupérer mon token:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
procedure TExplorerForm.RESTDemandeToken;
var
  vRESTClient : TRESTClient;
  VRESTREquest: TRESTRequest;
  vUrl,
  vGrantType,
  vTenantID,
  vClientID,
  vClientSecret,
  vRessource ,
  vScope,
  vToken,
  vrefreshToken : String;
begin
  vTenantID     := 'TenantId';
  vClientID     := 'ClientId';
  vClientSecret := 'ClientSecret';
  vGrantType    := 'client_credentials';
  vRessource    := 'https://database.windows.net/';
  vScope        := 'https://management.azure.com/.default';
  vUrl          := 'https://login.microsoftonline.com/' + vTenantID + '/oauth2/v2.0/token';
 
  vRESTClient   := TRESTClient.Create(vUrl);
 
  try
    ConfigureProxy(vRESTClient);
 
    VRESTREquest        := TRESTRequest.Create(vRESTClient);
    VRESTREquest.Method := TRESTRequestMethod.rmPOST;
 
    VRESTREquest.Params.AddItem('grant_type',     vGrantType,    TRESTRequestParameterKind.pkGETorPOST, [], TRESTContentType.ctAPPLICATION_X_WWW_FORM_URLENCODED);
    VRESTREquest.Params.AddItem('client_id',      vClientID,     TRESTRequestParameterKind.pkGETorPOST, [], TRESTContentType.ctAPPLICATION_X_WWW_FORM_URLENCODED);
    VRESTREquest.Params.AddItem('scope',          vScope,        TRESTRequestParameterKind.pkGETorPOST, [], TRESTContentType.ctAPPLICATION_X_WWW_FORM_URLENCODED);
    VRESTREquest.Params.AddItem('client_secret',  vClientSecret, TRESTRequestParameterKind.pkGETorPOST, [], TRESTContentType.ctAPPLICATION_X_WWW_FORM_URLENCODED);
    VRESTREquest.Params.AddItem('ressource',      vRessource,    TRESTRequestParameterKind.pkGETorPOST, [], TRESTContentType.ctAPPLICATION_X_WWW_FORM_URLENCODED);
 
    try
      VRESTREquest.Execute;
    except
      ShowMessage('Problème sur la requête RESTDemandeToken');
    end;
 
    if VRESTREquest.Response.StatusCode = 200 then begin
      if not vRESTRequest.Response.GetSimpleValue('access_token', fToken)
        then ShowMessage('La réponse ne comprend pas de Token');
    end;
 
    fTime := IncSecond(Now, 3599);
    Token_edt.Text := pToken;
 
  finally
    vRESTClient.Free
  end;
end;

J'ai trouvé des exemples en Python, que j'ai essayé d'adapter. (Main_fdc est de type TFDConnection)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
procedure TExplorerForm.BeforeConnect(Sender: TObject);
var
  vLen,
  vIdx     : integer;
  vLen2    : LongWord;
  vToken,
  vToken_s : string;
  vToken_b : string;
  vODBCAdv : string;
begin
  inherited;
 
  // test "ODBCAdvanced" SQL_COPT_SS_ACCESS_TOKEN (=1256)
  vToken_s := pToken; // Le jeton "database.windows.net"
  vLen := length(vToken_s);
  SetLength(vToken_b,vLen*2);
 
  for var i:=1 to vLen do begin
    vToken_b[(i*2)-1] := vToken_s[i];
    vToken_b[i*2]     := #0;
  end;
 
  vLen2 := Length(vToken_b);
 
  // ODBCAdvanced = attributs de connexion "before" de python ?
  vODBCAdv := 'ODBCAdvanced=1256='
            + vLen2.ToHexString(4)
            + vToken_b;
 
  vIdx := Main_fdc.Params.IndexOfName('ODBCAdvanced');
  if vIdx=-1 then vIdx := Main_fdc.Params.Add('ODBCAdvanced=');
  Main_fdc.Params[vIdx] := vODBCAdv;
 
  // Doc Microsoft stipule qu'il ne faut pas d'Authentication, UID, PWD et Trusted connexion pour SQL_COPT_SS_ACCESS_TOKEN
  vIdx := Main_fdc.Params.IndexOfName('User_Name');
  if vIdx> -1 then Main_fdc.Params.Delete(vIdx);
  vIdx := Main_fdc.Params.IndexOfName('Password');
  if vIdx> -1 then Main_fdc.Params.Delete(vIdx);
 
  vIdx := Main_fdc.Params.IndexOfName('Trusted_Connection');
  if vIdx> -1 then Main_fdc.Params.Delete(vIdx); // n'est pas dans les Params de FiredDac, mais figure bien dans la chaine de connexion finale 
                                                 // voir : FireDac.Phys.ODBCWrapper - TODBCConnction.Connect
 
end;

Mais j'ai un "Login failed for user ''."

Voici un extrait de la chaine de connexion finale

Citation:

DRIVER=ODBC Driver 17 for SQL Server;Server=xxxxxx;Database=xxxxx;WSID=xxxxx;Trusted_Connection=No;Encrypt=Yes;APP=xxx;1256=0A0Ee#0y#0J#00#0e...

Je me pose plein de questions :
- Le format attendu par SQL server pour le type SQL_IS_POINTER est-il satisfait par la partie "vLen2.ToHexString(4)" (0A0E) ?
- Nous n'avons pas la main sur la chaine de connexion finale (par exemple Trusted_Connection=No est forcée), est-ce le bon moyen, je suis perturbé le "attrs_before" de python

Code:

db = create_engine(SAconnString, connect_args={'attrs_before': {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct}})

-

- que j'ai interprété par un complément "ODBCAdvanced", mais cela peut-il convenir pour envoyer l'attribut SQL_COPT_SS_ACCESS_TOKEN ?

Bref, si quelqu'un s'est déjà frotté au sujet ou a une vue plus expérimentée que la mienne sur le sujet, toute aide serait vraiment bien accueillie :D

Infos complémentaires :
le lien microsoft pour SQL_COPT_SS_ACCESS_TOKEN
et exemple python

Merci
Renaud

08/03/2022, 17h12
SergioMaster

Bonjour,

Je ne me suis pas frotté à ça mais je suis en plein REST
Juste une suggestion, tenter d'obtenir le token en utilisant REST Debugger ce qui permettrait de tout contrôler avant de créer une RestRequest en direct
08/03/2022, 17h33
ShaiLeTroll

Vu la documentation MSDN, la présence de Trusted_Connection sera effectivement nuisible, il faudra travailler à le faire disparaître.

Ce buffer ne doit pas être passé en chaine comme élément de chaine de connexion, comme compris, c'est une astuce de Python pour renseigner un paramètre avant la connexion.
En plus, une String Delphi c'est de l'Unicode ainsi récupérer le binaire contient déjà les 00 de padding façon UTF16 / UCS-2

Avant que se produise TODBCConnction.Connect regarde su tu peux avoir accès à FireDAC.Phys.ODBCWrapper.TODBCLib.SQLSetConnectAttr

1 pièce(s) jointe(s)

ça avance

Bonjour,

Merci à vous deux d'avoir répondu.

Pour le RESTDebuger nous l'utilisons aussi, c'est un bon outil, d'ailleurs Sergio je me demande si ce n'est pas suite à ta lecture que nous y sommes venus.

Le sujet avance sans toutefois être résolu.

J'ai suivi tes pistes ShaiLeTroll, et j'ai découvert également le Monitoring FireDac, bien pratique !
Pour ceux que ça intéresse : mise en œuvre bien décrite ici.

Aller, on partage :

1° SQLSetConnectAttr

Citation:

regarde si tu peux avoir accès à FireDAC.Phys.ODBCWrapper.TODBCLib.SQLSetConnectAttr

Main_fdc est mon TFDConnexion vers mon instance managée Azure SQL Server.
Code:

1 2 3 4 5 6 7 const SQL_COPT_SS_ACCESS_TOKEN = 1256; var vToken : string; ... TFDPhysODBCConnectionBase(Main_fdc.ConnectionIntf).ODBCConnection.SetAttribute(SQL_COPT_SS_ACCESS_TOKEN, SQLPointer(vToken),0)
ODBConnection est créé assez tardivement pendant le process de connexion (voir TFDPhysODBCConnectionBase.InternalConnect)
je ne pense pas qu'on puisse y accéder sans personnaliser l'unité "FireDAC.Phys.ODBCWrapper"

SQLPointer(vToken) : J'ai éliminé tout le travail en "byte" de mes premiers essais pour rester en string suivant ton conseil, le buffer passe bien comme SQL server l'attend.

2° intervenir au bon moment
pour
- purger la chaine de connexion du "trusted_connexion"
- faire les SetAttribute avec un handle existant.

J'ai choisi de personnaliser le code de deux unités FireDAC, je ne vois pas d'autre solution.
Etant donné que cette pratique est à mes "risques & périls", il faut être un peu organisé pour être à même de réagir aux évolutions de ces unités lors des futures mises à jour.

J'ai donc créé un event sur lequel je pourrais me "brancher" juste avant l'envoi de la chaine finale de connexion.
Cet event doit me permettre
- de récupérer le ODBCConnexion pour y appliquer le SetAttribute
- de retravailler la chaine de connexion qui sera finalement envoyée.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 unit FireDAC.Phys.ODBCWrapper; ... interface type ... TBeforeODBCConnectEvent = procedure (aODBCConnection:TODBCConnection;var aConnString:string) of object; TODBCConnection = class(TODBCHandle) public ... property BeforeODBCConnect : TBeforeODBCConnectEvent read fBeforeODBCConnect write fBeforeODBCConnect; implementation ... function TODBCConnection.Connect(const AConnString: String): String; // retrait du "const" d'origine pour la propagation des modifications faites sur l'event. begin if Assigned(fBeforeODBCConnect) then fBeforeODBCConnect(self,aConnString); Result := DriverConnect(aConnString, SQL_DRIVER_NOPROMPT, 0); // l'instruction d'origine end;
Il reste à initialiser l'évènement après la création du ODBCConnection, laquelle à lieu dans la procedure "TFDPhysODBCConnectionBase.InternalConnect;"
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 unit FireDAC.Phys.ODBCBase; interface uses ... FireDAC.Phys.ODBCWrapper; // vérification, l'unité est déjà dans les uses de l'interface (on a besoin du type "TBeforeODBCConnectEvent") type ... TFDPhysODBCConnectionBase = class(TFDPhysConnection) ... public property BeforeODBCConnect : TBeforeODBCConnectEvent read fBeforeODBCConnect write fBeforeODBCConnect; implementation procedure TFDPhysODBCConnectionBase.InternalConnect; ... begin oODBCLib := TFDPhysODBCDriverBase(DriverObj).FODBCLib; if InternalGetSharedCliHandle() <> nil then begin pCliHandles := PFDPhysODBCCliHandles(InternalGetSharedCliHandle()); FODBCEnvironment := TODBCEnvironment.CreateUsingHandle(oODBCLib, pCliHandles^[0], Self); FODBCConnection := TODBCConnection.CreateUsingHandle(FODBCEnvironment, pCliHandles^[1], Self); end else begin FODBCEnvironment := TODBCEnvironment.Create(oODBCLib, Self, GetODBCVersion()); FODBCConnection := TODBCConnection.Create(FODBCEnvironment, Self); end; FODBCConnection.ExceptionClass := GetExceptionClass; FODBCConnection.OnGetMaxSizes := GetStrsMaxSizes; {$IFDEF FireDAC_MONITOR} InternalTracingChanged; {$ENDIF} ODBCConnection.BeforeODBCConnect := fBeforeODBCConnect; ... end;
3° BeforeODBCConnect
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 procedure TMainDatas.DriverMSSQL17DriverCreated(Sender: TObject); var vIdx : integer; begin if SQLAuthMode = SQL_COPT_SS_ACCESS_TOKEN then begin TFDPhysODBCConnectionBase(Main_fdc.ConnectionIntf).BeforeODBCConnect := BeforeODBCConnect; // retirer les params indésirables pour SQL_COPT_SS_ACCESS_TOKEN vIdx := Main_fdc.Params.IndexOfName('User_Name'); if vIdx<>-1 then Main_fdc.Params.Delete(vIdx); vIdx := Main_fdc.Params.IndexOfName('Password'); if vIdx<>-1 then Main_fdc.Params.Delete(vIdx); end; ... end; procedure TMainDatas.BeforeODBCConnect(aODBCConnection:TODBCConnection;var aConnString:string); var vIdx : integer; vConnString, vLeft, vRight : string; begin inherited; aODBCConnection.SetAttribute(SQL_COPT_SS_ACCESS_TOKEN, SQLPointer(myToken),0); // Trusted_Connection est forcée par FireDac en plus des Params visibles, // obligé d'intercepter la chaine de connexion avant qu'elle ne soit envoyée vConnString := aConnString; vIdx := Pos('trusted_connection',lowercase(vConnString)); if vIdx>0 then begin vLeft := LeftStr(vConnString,vIdx-1); vRight := RightStr(vConnString,Length(vConnString)-vIdx); vIdx := Pos(';',vRight); delete(vRight,1,vIdx); aConnString := vLeft+vRight; end; end;
Le monitoring de FireDac :
Pièce jointe 615063

Le SetAttrribute est ok, je ne m'alarme pas du "unknown" puisque la constante n'est pas connue de FireDac (les constantes SQL_COPT_SS_xxxx sont dans FireDAC.Phys.ODBCCLi et il manque les dernières de Microsoft dont SQL_COPT_SS_TOKEN_ACCESSS)
la chaine connexion est comme on la souhaite ...

Oui, ça va marcher !!!

Et bin non 8O, surprise et déception !
J'ai une violation d'accès dans le module "ucrtbase.dll" après l'appel de Lib.SQLDriverConnect.
Code:

1 2 3 4 5 function TODBCConnection.DriverConnect(const AConnString: String;ADriverCompletion: SQLUSmallint; AParentWnd: SQLHWnd): String; ... Check(Lib.SQLDriverConnect(FHandle, AParentWnd, PSQLChar(AConnString), SQL_NTS, PSQLChar(Result), C_RETURNED_STRING_MAXLEN, iOutConnStrLen, ADriverCompletion));
ça ressemble à une bête faute mémoire
- j'ai vérifié la taille du buffer pour le token, longueur string x 2, ok.
- J'ai inhibé la reprise de aConnString par mon event, la violation persiste.
- J'ai réinstallé le dernier driver Microsoft ODBC Driver 18 for SQL server (x64)
- En mode pas à pas les différents arguments passé à Lib.SQLDriverConnect semblent corrects.

je sèche

Pour l'heure je me rabat sur une connexion "Active Directory Service Principal" qui fonctionne sans encombre, mais si vous avez des idées ...

En tout cas, merci
Bien à vous
Renaud

10/03/2022, 07h04
SergioMaster

Bonjour,

Citation:

Envoyé par AndMirabel

Pour le RESTDebuger nous l'utilisons aussi, c'est un bon outil, d'ailleurs Sergio je me demande si ce n'est pas suite à ta lecture que nous y sommes venus.

Bien content que mes élécubriations servent à quelque chose si c'est la cas ;)

Je suis sûr d'avoir vu passé une question sur le problème de l'OAuth2 quelque part le hic c'est que ma mémoire me fait défaut, cela doit faire un bail et peut-être pas en conjonction Azure
Il y a quand même des trucs à trouver des que l'on passe en anglais :mouarf:
tutoriaux de
Felix Colibri, DropBox
Alister Christie, TWitter
à lire aussi les documentations de Cdata, oui, il y a des composants Cdata Firedac Amazon xxxx mais aussi d'autres qui utilisent OAuth dans le tas il y aura peut-être une réponse :koi:

enfin après une autre petite recherche les unités Dmintuit.pas et OAuth2.intuit.pas dans ce dépôt devrait fournir un bon exemple de code même si ce n'est que REST

SQLPointer c'est une fonction ? un transtypage
Ayant une version Pro, je n'ai presque rien de FireDac

Attention, il ne faut pas oublier
Code:

1 2 3 4 5 typedef struct AccessToken { DWORD dataSize; BYTE data[]; } ACCESSTOKEN;
soit
Code:

1 2 3 4 5 6 type PAccessToken = ^TAccessToken; TAccessToken = packed record dataSize: DWORD; data: PByte; end;
je ferais donc une fonction qui ne fonctionne qu'en UNICODE
Code:

1 2 3 4 5 procedure StringToAccessToken(const S: UnicodeString: out A: TAccessToken); begin A.dataSize := Length(S) * 2; A.data := @S[1]; end;
ça donnerait un truc du genre
Code:

1 2 3 4 5 6 7 var AccessToken: TAccessToken; begin StringToAccessToken(vToken, AccessToken); ....SetAttribute(SQL_COPT_SS_ACCESS_TOKEN, @AccessToken,0);

15/03/2022, 09h29
AndMirabel

on suspend, merci à tous

Merci ShaiLeTroll, effectivement ça va mieux comme ça !

Malheureusement nous arrivons dans une nouvelle impasse, les journaux du serveur SQL nous montre bien la tentative de connexion mais lève une erreur de reconnaissance du Token.
Nous sommes sur des Token que nous acquérons, en revanche il doit rester une subtilité dans la manière dont on l'envoie à Azure.

Pour l'heure nous laissons de coté cette méthode en partie défrichée, mais ne manquerons pas de revenir ici le cas échéant.

Merci

Renaud
15/03/2022, 10h20
ShaiLeTroll

Pour SQL Azure, je ne sais pas mais pour GRAPH, POP et IMAP, j'ai bossé sur le XOAuth2
Tu utilise en client_credentials, c'est du droit applicatif si je me trompe pas, le scope semble en plus cohérent avec ça, c'est ton administrateur Azure qui t'a configuré ton application sur le Portail ?
Lorsque je m'y suis mis, j'ai pas mal tatonné, le portail Azure pour créer ses applis, j'en ai fait une documentation parce que même pour un Administrateur, cela n'a rien d'évident quand on y a jamais touché.

As-tu essayé du droit délégué, rien que pour tester ?
J'ignore même si "management.azure" existe en droit délégué et pas uniquement en droit applicatif.
Donc si je n'utilise pas un environnement type "Microsoft 365 Developer Program" où là on peut faire ce que l'on veut pendant période limitée, où j'ai pu testé le droit applicatif dans un environnement Azure Virtuel.

En Debug, le droit délégué, c'est ce que je fais tout le temps, je n'ai pas assez de droit sur l'azure de la boite pour être en droit applicatif alors je triche avec des droits délégués, je lance une page d'auth dans une application annexe, qui fait la séquence Web Redirection, AuthCode, AccessToken/RefreshToken ensuite mon programme Server lui est capable de manger un Token de l'extérieur lorsqu'il est lancé dans un mode de débogage.
Non parce que le Mail Bomber pour remplir la boite mail factice du Azure Virtuel c'est bien pour les essais et la volumétrie, genre 50000 mails aspiré par le programme en 2 jours, mais aspirer une vraie boite mail avec des milliers de mail aux contenus très variés, cela change un peu tout d'un mail bomber bidon.