Open File odbcad32.exe – this is ODBC Administrator:
For that run command prompt and type in command prompt odbcad32.exe, after open ODBC Data Sources then go to tab "File DSN "
Then click button "Add" then Select in list driver that you need (in my example it's SQL Native Client – driver for SQL Server).
Then enter the name of file dsn that will be create click "Next" then "Finish".
After that will be opened window "Creating Data Source for SQL Server".
Enter description if you want and (Server – that name of SQL Serevr Instance just select from list) this field required then click next.
After that I choose "…Windows Authentication" radio button not "…SQL Server authentication" radio button because did not use user name and login to connect in local machine if you connect to your server only with SQL Server authentication then you need select second radio button "SQL Server authentication " and enter user name and password in two textboxes under. Then click next and click checkbox "Use database by default" - it's allowed you select database that you need to connect after select database from list click next.
Then Click finish.
Then Click button "Test Connection".
If everything was fine will be message in window box "Test finished successfully!".
After that click "OK" and your file DSN created it appears in tab "File DSN" with name that you give it.
The DSN file will be situated in folder Documents for current user (for Windows Vista and higher you can found it there if Windows XP or Windows Server 2003 maybe it's folder Document and settings I don't have installed old OS and didn't check it) and will have extention *.dsn. Open file in notepad.
For my example file looks like below:
DRIVER=SQL Native Client
APP=Операционная система Microsoft® Windows®
Description=SQL Server test odbc connection
DRIVER – driver for your sql server (for MS SQL Server it's SQL Native Client for other database driver will be different )
UID – authenticated user in OS
DATABASE – database that selected by default
WSID – symbol name of computer
APP – current Operating System
Trusted connection – "yes" - no need authentication parameters authentication by OS
SERVER - full SQL Server Name (Machine Name/SQL Server Instance Name)
Description – text that you typed in window "Creating Data Source for SQL Server".
After that you can copy from line DRIVER to line Description and insert in your ConnectionString property of OdbcConnection in Visual Studio.
For my example it's looks like:
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = @"DRIVER=SQL Native Client;UID=Alexander;DATABASE=TAM_TEST;WSID=ALEXPC;APP=Операционная система Microsoft Windows;Trusted_Connection=Yes;SERVER=ALEXPC\SQLEXPRESS;Description=SQL Server test odbc connection;";
OdbcCommand command = new OdbcCommand();
command.Connection = conn;
command.CommandText = "Select top 10 question from tests";
After connection is opened you can work as usual with OdbcDataReader or OdbcDataAdapter.
Be careful that need insert symbol @ before the ConnectionString begin. And need separate parameters with ";" because in dsn file each parameter situated at new line without symbol ";".