Islander’s Analog life….

The new SQL Server Native Client 10.0 app appears not to return the password back when using Delphi’s ADO PromptDataSource

Update: 6/20/2008
There is definitely a bug in the Dialog not in the PrompDataSource.  The solution is posted at the end of the article.

Microsoft SQL Server 2008 installs a new OLE DB Provider called: “SQL Server Native Client 10.0” Here is the Data Link Properties image:

Bringing up the ADO Connection Dialog:

CS  := PromptDataSource( Handle, "");

One change I can see is that using Delphi’s PrompDataSource function, it returns a different string than previous providers.  The Password field seems to be missing:

'Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=sa;Initial Catalog=XIMS;Data Source=(local);Initial File Name="";Server SPN=""'

Here is the output from PrompDataSource using Microsoft OLE DB  Provider for SQL Server:

'Provider=SQLOLEDB.1;Password=1234;Persist Security Info=True;User ID=sa;Initial Catalog=Rome;Data Source=(local)’

Here is an image of the old Data Link Properties editor:

The new Dialog has three key differences :

1. You can used mix mode authentication (nothing new here) but also use Windows NT Integrated Security and specify a Server SPN(service principal name).  This is quite flexible.  For more info click here
2. Be able to attache a database file as a database Name - I am guessing this is here due to all the enhancements SQL Server 2008 offers in regards to backing up, restore, compression and encryption database files.
3. Be able to Change the database password directly from this page.

If you find out how to retrieve the “whole” string let me know.

Solution: go into the ALL tab and make sure you set Persist Security Info to TRUE the default (today) is FALSE (thanks Ken!)

You might be interested in finding out what’s new in SQL Server 2008

I predict that SQL server 2008 is going to be a huge hit. It has been designed for performance and scalability from the start.

Some of the new features:

• Table Valued Parameters - stored procedures will accept a table parameter.  ADO.NET has been modified so from C#, etc you can create a new variable called ‘SqlDBType.Structure‘ and pass this onto your stored procedures.  This is huge!  No more temporary tables, or cursors to emulate the same behavior.
  CREATE TYPE CustomersTableType AS TABLE (NAME VARCHAR(25), LASTNAME VARCHAR(25));
CREATE PROCEDURE UpdateCustomers(@CUSTOMERS CustomersTableType REAONLY)
AS
BEGIN
INSERT INTO CUSTOMERS (NAME, LASTNAME)
SELECT NAME, LASTNAME FROM @CUSTOMERS;
END;
• New date/time data types: date, time, and also same times but time zone aware
  DATE - holds date from 1/1/1 to 12/31/9999
TIME - holds timeaccurate up to 100 nanoseconds
DATETIMEOFFSET - time zone aware.  Stores value in UTC format
DATETIME2 - holds date & time with larger precision
• New geo-spatial (latitude/longitude) data types -SQL server understand natively coordinates so you can create location aware applications.  For instance, get me all the customers within a 10 miles radius
  (more…)