2

I use PromptDataSource to allow users to setup a database connection. 

connectionString := ADOdb.PromptDataSource(0, '');

However I can't get it to work with "Allow saving password" when using the SQL Server Native Client 10.0 provider:

enter image description here

PromptDataSource return a string with Persist Security Info=False, and the password is omitted, when using that provider:

Provider=SQNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=MGH;Initial Catalog="";Data Source=stackoverflow.com;Initial File Name="";Server SPN=""

Other providers that I've tried worked, what can I do to fix this?

Note: I believe the provider is installed with MS SQL Server 2008 R2.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
MGH
  • 1,189
  • 1
  • 10
  • 18

3 Answers3

4

Edit: I forgot to include my answer: cannot be done.

Not the answer you want to hear, but i am going to guess that it is an intentional bug - and you will not convince Microsoft to fix it.

i can confirm that user supplied passwords work for:

  • Microsoft.Jet.OLEDB.4.0: Microsoft Jet 4.0 OLE DB Provider (works)
  • MSOLAP: Microsoft OLE DB Provider for Analysis Services 10.0 (works)
  • MSDASQL: Microsoft OLE DB Provider for ODBC Drivers (works)
  • MSDAORA: Microsoft OLE DB Provider for Oracle (works)
  • SQLOLEDB: Microsoft OLE DB Provider for SQL Server (works)
  • MSDataShape: MSDataShape (works)
  • SQLNCLI: SQL Server Native Client (works) (Released with 2005)

and fail for

  • SQLNCLI10: SQL Server Native Client 10.0 (fails) (Released with 2008)
  • SQLNCLI11: SQL Server Native Client 11.0 (fails) (Released with 2012)

It looks like Microsoft's broken by default policy again.

Bonus Reading

More of Microsoft's broken by default things:

Community
  • 1
  • 1
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • 1
    I still hope it might be workarounded by invoking that dialog through the API. – TLama Jun 13 '13 at 15:17
  • Holy hell, i thought that *was* a straight API. Looking now that the [`IBPromptInitialize` interface](http://msdn.microsoft.com/en-us/library/windows/desktop/ms725392(v=vs.85).aspx); would forcing the user to *only* be allowed to select `SQLOLEDB` be an acceptable workaround? Because you can limit the user's choices to specified ProgIDs (e.g. only SQLOLEDB). – Ian Boyd Jun 13 '13 at 21:00
  • I'd like to allow use of the provider, so I'll make another option to provide password and patch the ConnectionString. – MGH Jun 14 '13 at 08:16
0

After filling Dataling dialog as usually, go to Dataling Properties last tab "All" and fill "Extended properties" to

Trusted_Connection=no;Server=..........;Database=.....

and "Persist Security Info" to "True" too, if not filled to true as default (Native client 10 and 11 fill it to False itself after every password changing). After it use this code (or other language equivalent), because "Integrated Security" with empty string value is wrong property for native client provider:

ConnectionString := PromptDataSource(Application.Handle, ConnectionString);
if ContainsText(ConnectionString, 'SQLNCLI10') or ContainsText(ConnectionString, 'SQLNCLI11') then
begin
 ConnectionString := StringReplace(ConnectionString, 'Integrated Security="";', '', [rfReplaceAll, rfIgnoreCase]);
end;
Fredrik Widerberg
  • 3,068
  • 10
  • 30
  • 42
Martin
  • 1
0

I changed "Persist Security Info" in "All" Tab to True and set Password there. After that I've got password saved enter image description here

Danil
  • 701
  • 8
  • 7