I am working on a MSI project and I need to create a SQL Native Client DSN to connect to a SQL Server 2005 Express server. On the DSN, I need to specify a username and password for SQL Auth. Also I would like to have the end user to be able to bring up a list of SQL Express servers on the network, choose the one they want to connect to; it should be only one, but can be named anything.
You can create a DSN, and specify the target server name by having the Server property using the ODBC Resources view. However, "user" and "password" for your odbc connection must be the parameters of your application. So your application must pass "user" and "password" to odbc DSN for making the connection to databases server.
If you are OK to bring up a list of all versions of Microsoft SQL Servers, you can use the built-in SQLLogin dialog that will be automatically displayed when you add a connection in the SQL Scripts view. You will need to delete the controls you do not want to display, and delete the Next control event that will execute the ISSQLServerValidate action using the Dialogs view.
The user name setting is stored as 'LastUser' and the authentication type is stored as 'Trusted_Connection' in the registry. So you will need to add the following properties for your DSN in the ODBC view:
You will also need to set the value of the MY_DSN_TRUSTED_CONNECTION property based on the value of the IS_SQLSERVER_AUTHENTICATION property after you click on the Next button on the SQLLogin dialog. The MY_DSN_TRUSTED_CONNECTION property needs to be set to 'Yes' for the Windows authentication, or an empty string if otherwise.
