This page demonstrates the steps you need to connect to SQL Server in Zeppelin. There are many ways to implement this, for example SQL Server interpreters in GitHub. In this page, I am going to use the JDBC driver to connect to SQL Server instead of using third party interpreters.
For authentication, I am using database login (SQL Server authentication) instead of Windows login since my machine is not part of any domain. If you want to setup Kerberos authentication, please refer the following link about how to configure ktab file name and SPN:
If you have not installed Zeppelin, follow the link below to setup your local instance:
Java JDK is also required, which is included as part of the above installation guide.
Connect to SQL Server
Setup SQL Server account
In my machine, I’ve setup one SQL Server login in my local SQL Server instance:
The above credential is going to be used when setting up interpreter.
In Zeppelin website, click Interpreter menu.
Click create button.
Input the following:
- Interpreter Name: tsql
- Interpreter group: jdbc
Setup the following properties:
- default.driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
- default.password: zeppelin
- default.url: jdbc:sqlserver://localhost
- default.user : zeppelin
- zeppelin.jdbc.auth.type: SIMPLE
- artifact: com.microsoft.sqlserver:mssql-jdbc:6.5.1.jre8-preview
For the artifact setting, you can use local lib file path or using groupid:artifact id:version. In my case, I am using the following SQL Server JDBC library.
Click Save button to save this new interpreter.
Now you can create a new note with the new interpreter (%tsql).
The following is a sample code I use:
select * from sys.all_objects where type='U'
The screenshot below shows the result:
It’s very easy to use JDBC to connect to SQL Server or any other JDBC compatible databases, for example, Oracle and Teradata.
Once you can connect to the database, you can then do analytics easily in Zeppelin.