command line to create data source with sql authen

Question

I'm looking to run a batch file on windows xp professional which creates a system odbc data source for a sql server connection. I can do that with:

ODBCCONF.exe CONFIGSYSDSN "SQL Server" "DSN=Kappa| Description=Kappa Data Source | SERVER=10.100.1.10 | Trusted_Connection=Yes | Database=subscribers"

However I need a way to set the sql server authentication to be "With SQL Server authentication using a login ID and password entered by the user." to be set and preset the login id and pass.

Any ideas on how to do this?

thanks in advance

Answer 1

You create the DSN the same way for both trusted and non trusted. It is when you use the ODBC connection string that you specify that you want to use trusted connections.

DsnString = "ODBC;DSN=Kappa;Database=MyDBName;Integrated Security=SSPI;Trusted_Connection=True"

Answer 2

ODBCConf ConfigSysDSN "SQL Server" "DSN=xxxxx|SERVER=yyyyy|Trusted_Connection=No"

seemed to work for me.

Answer 3

I hope this helps someone, i found that i could not store the username and password. However, I was able to make a ODBC connection without those. The problem was that the connection kept returning an error about how it could not locate the data source name or driver. Only after I removed the spaces from the attribute string could my programs use the odbc connection.

So

ODBCCONF.exe /a { CONFIGSYSDSN "SQL Server" "DSN=dsnname | SERVER=yourservername | Database=Finance"}

should become

ODBCCONF.exe /a { CONFIGSYSDSN "SQL Server" "DSN=dsnname|SERVER=yourservername|Database=Finance"}

Answer 4

create a .reg file with the parameters and merge it in with regedit.exe

Tip: export an existing ODBC profile from the registry to help get the syntax and field names correct