What are the differences between User DSNs and System DSNs? What about Machine DSNs and File DSNs?




User vs System

User and System DSNs are so named because of their accessibility. System DSNs are recommended for most purposes.

User and System DSNs may each be either File or Machine DSNs.

User DSN - A User DSN is only available for use by the user who created it.

System DSN - A System DSN is available for use by any user.

Machine vs File

Machine and File DSNs are so named because of their storage locations and management methods. Machine DSNs are recommended for most purposes.

File and Machine DSNs may each be either User or System DSNs.

Machine DSN - A Machine DSN is saved to a repository file (or files) determined by the environment's ODBC driver manager.

File DSN - A File DSN is a set of DSN connection attributes that is saved to a standalone text file named by the DSN with a .DSN extension (e.g., for a DSN "my_dsn", the filename would typically be "my_dsn.dsn)".


Typical Storage Locations

Note: The following locations are defaults, but are not the only places that these DSNs or files may be found.

Mac OS X

Machine DSNs File DSNs
System DSNs /Library/ODBC/odbc.ini

$ODBCINI

/etc/odbc.ini
/Library/ODBC/ODBCDataSources?/
User DSNs /Users/*/Library/ODBC/odbc.ini

/Users/*/.odbc.ini
/Users/*/Library/ODBC/ODBCDataSources?/


Unix-like OS (AIX, HP-UX, Linux, Solaris, etc.)

Machine DSNs File DSNs
System DSNs $ODBCINI

/etc/odbc.ini
User DSNs ~/.odbc.ini


Windows

Machine DSNs File DSNs
System DSNs HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI C:\Program Files\Common Files\ODBC\Data Sources\
User DSNs HKEY_CURRENT_USER\Software\ODBC\ODBC.INI