The following snippet always fails to create ODBC DSN
[DllImport("ODBCCP32.dll")]
private static extern bool SQLConfigDataSource(IntPtr hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
public bool AddSql2005DSN(string dsName, string server, string db, string user, string password)
{
StringBuilder sb = new StringBuilder();
sb.Append("DSN=" + dsName + "\0");
sb.Append("Server=" + server + "\0");
sb.Append("Database=" + db + "\0");
sb.Append("Trusted_Connection=no\0");
sb.Append("UID=" + user + "\0");
sb.Append("PWD=" + password + "\0");
return SQLConfigDataSource((IntPtr)0, 4, "SQL Native Client", sb.ToString());
}
However, if bolded lines are removed, it creates the DSN using integrated authentication ("Trusted_Connection=no\0" is ignored.)
If I'm creating the DSN using interface, it accepts user & password and creates it successfully.
Any ideas why that could be
Creating ODBC DSN for SQL Native Client fails for not-integrated authentication
Aaron_Liu
Thanks a lot for the replay.
I noted that it's not stored in the odbc.ini registry key. My hope was that it's stored 'somewhere else'.
Thanks again.
nitinbourai
You cannot store UID and PWD in an ODBC datasource, this is why. For example, you can create a DSN using the user interface but if you look at the DSN stored in the registry (under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI for example) the UID and PWD are not stored.
The userid and password prompt in the user interface is only used temporarily to allow connecting to SQL to read additional things from SQL like the default database for the user.
So with ODBC it is always required when you connect using a DSN that the caller supply UID and PWD if they want to use standard login during connection time. This is done for security reasons you don't want to store user id and password in the registry it's not safe.