Discussion :

[AndMirabel]

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).




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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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




Merci
Renaud

[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

[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

[AndMirabel]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :


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 , surprise et déception !
J'ai une violation d'accès dans le module "ucrtbase.dll" après l'appel de Lib.SQLDriverConnect.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SergioMaster]

Bonjour,

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
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

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

[ShaiLeTroll]

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 c :

Sélectionner tout - Visualiser dans une fenêtre à part

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

soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[AndMirabel]

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