This tutorial shows how to set up a data source and connect to a Microsoft® SQL Server® database using the Database Explorer app or the command line. This tutorial uses the Microsoft ODBC Driver 13.1 for SQL Server to connect to a Microsoft SQL Server 2016 Express database.
The ODBC driver is typically preinstalled on your computer. For details about the driver installation or troubleshooting the installation, contact your database administrator or refer to your database documentation on ODBC drivers. For information about the Microsoft ODBC Data Source Administrator, see Driver Installation.
Database Toolbox™ no longer supports connecting to a database using a 32-bit driver. Use the 64-bit version of SQL Server. If you have issues working with the ODBC driver, use the JDBC driver instead. For details, see Microsoft SQL Server JDBC for Windows. For details about working with the 64-bit version of Windows®, see https://www.mathworks.com/products/matlab/preparing-for-64-bit-windows.html.
The Database Explorer app accesses the Microsoft ODBC Data Source Administrator automatically when you configure an ODBC data source. Alternatively, you can access the Microsoft ODBC Data Source Administrator using the
Open the Database Explorer app by clicking the Apps tab on the MATLAB® Toolstrip. Then, on the right of the Apps section, click the Show more arrow to open the apps gallery. Under Database Connectivity and Reporting, click Database Explorer. Alternatively, enter
databaseExplorer at the command line.
In the Data Source section, select Configure Data Source > Configure ODBC data source.
In the ODBC Data Source Administrator dialog box, you define the ODBC data source.
When setting up an ODBC data source, you can specify a user data source name (DSN) or a system DSN. A user DSN is specific to the person logged into a machine. Only this person sees the data sources that are defined on the user DSN tab. A system DSN is not specific to the person logged into a machine. Any person who logs into the machine can see the data sources that are defined on the system DSN tab. Your ability to set up a user DSN or system DSN depends on the database and ODBC driver you are using. For details, contact the database administrator or refer to the ODBC driver documentation.
Click the System DSN tab, and then click Add.
The Create New Data Source dialog box opens and displays a list of installed ODBC drivers.
SQL Server Native Client 11.0.
The name of the ODBC driver can vary.
In the Create a New Data Source to SQL Server dialog box, enter an appropriate name for the
data source. You use this name to establish a connection to your database.
Here, in the Name box, enter
Server as the data source name. In the
Description box, enter
Server as the description. From the
Server list, select the database server for this
data source to use. Consult your database administrator for the name of your
database server. Click Next.
You can set up an ODBC data source with or without Windows authentication.
If you want to connect to SQL Server using Windows authentication, select With Integrated Windows Authentication. Then click Next.
Or, if you want to connect to SQL Server without Windows authentication, select With SQL Server authentication using a login ID and password entered by the user. Enter your user name in the Login ID box and your password in the Password box. Then click Next.
In the Create a New Data Source to SQL Server dialog box, select Change the default
database to and enter the name of the default database on the
database server for connection. Here, use the database
toy_store. Then click
Click Finish to accept the default settings.
In the ODBC Microsoft SQL Server Setup dialog box, test your connection by clicking Test Data Source.
If the connection succeeds, the SQL Server ODBC Data Source Test dialog box opens and displays a message indicating the tests completed successfully. Click OK to close this dialog box. Click OK to close the ODBC Microsoft SQL Server Setup dialog box.
The ODBC Data Source Administrator dialog box shows the new data source under System Data Sources on the System DSN tab. Click OK to close the ODBC Data Source Administrator dialog box.
After you complete the data source setup, connect to the SQL Server database using the Database Explorer app or the command line with the native ODBC connection.
On the Database Explorer tab, in the Data Source section, click New Query.
In the Connect to a Data Source dialog box, connect with Windows authentication by selecting the data source you defined from the Data Source list. Leave the Username and Password boxes blank. Click Connect.
Or, connect without Windows authentication by selecting the data source you defined. Enter a user name and password. Click Connect.
The Catalog and Schema dialog box opens.
Select the catalog and schema from the Catalog and Schema lists. Click OK.
The Database Explorer app connects to the database and displays database tables in the Data Browser pane. A data source tab appears to the right of the pane. The title of the data source tab is the data source name that you defined during the setup. The data source tab contains empty SQL Query and Data Preview panes.
Select tables in the Data Browser pane to query the database.
Close the data source tab to close the SQL query and the database connection.
To close the database connection, close all tabs that have titles
beginning with the name of the corresponding data source. For
example, if the data source name is
Server, and two tabs named
MS SQL Server1 are open,
then close both tabs.
To close all database connections, close the Database Explorer app.
To connect with Windows authentication, connect to the database with the
authenticated ODBC data source name and a blank user name and password.
For example, this code assumes that you are connecting to a data source
MS SQL Server Auth.
conn = database('MS SQL Server Auth','','');
Or, to connect without Windows authentication, connect to the database with the ODBC data
source name. For example, this code assumes that you are connecting to a
data source named
MS SQL Server with the user name
username and the password
conn = database('MS SQL Server','username','pwd');
Close the database connection.