Microsoft SQL Server is a server-client software that runs on the Microsoft Windows operating system. It provides an environment for storing and retrieving tabular data, such as tables of information or business transactions. The platform enables users to manage their databases from any location with only one client license needed.
The “connect to sql server from linux using windows authentication” is a way to connect to SQL Server from Linux. The process includes using the Windows Authentication method, which can be found in the Microsoft documentation.
Everything would be the same in an ideal database administrator world. The workloads on all of the servers would be identical. They’re less difficult to handle. However, this is not the case. System administrators nowadays are responsible for a variety of contexts. This is especially true when running SQL queries from a Linux system. You’ll discover how to connect to SQL Server from Linux in this article!
A Python module is one approach to connect to SQL Server from Linux. But, before we get there, let’s talk about the atmosphere in which I work.
In this post, I’ll show you how to do this activity using Ubuntu 16.04 and SQL Server 2012 R2. However, the same method should work with other Linux variants and SQL Server versions. Administrators of databases, rejoice!
Prerequisites
You’ll need to install a few requirements before you can begin. To begin, you’ll need a Python module since you’ll be connecting to a SQL Server instance via Python. PyODBC is a popular Python tool for connecting to SQL databases. This module enables you to query SQL databases using an ODBC driver for SQL Server on Linux. Use pip to install the most recent version (the Python package manager).
> sudo pip install pyodbc
You may not have pip installed if this does not work. Install pip as follows:
The next step is to write a Python script. This one will be called sql server.py. To begin writing a Python script, start with a blank file.
Then, using your preferred editor, add the lines below. The shebang, followed by the path to the Python binary, indicates that this is a Python script to the interpreter. You can then use the import line to invoke the library functions from inside the pyodbc module. Keep this script safe.
!pyodbc import /usr/bin/python
Run the Python script once it has been created:
The pyodbc module has been successfully installed if this runs without errors.
Add the code to run a test query next. Create an ODBC string for this.
Here’s a nice place to start learning about ODBC strings.
The ODBC string is particular about what it accepts. It took some effort to figure out how to do this, but here’s how mine turned out. I’m using the connect() function given with the pyodbc module to send the ODBC string as an input.
pyodbc.connect = conn (‘DRIVER={FreeTDS};SERVER=server.domain.local;PORT=1433;UID=DOMAINuser;PWD=mypassword;DATABASE=mydatabasename;UseNTLMv2=yes;TDS Version=8.0;Trusted Domain=domain.local;’)
The majority of the ODBC string is obvious, but the double backslash for the UID is crucial. Always make sure that any backslashes in the ODBC string are escaped. Some of the parameters I use are also optional.
You may also use the SQL Server IP address instead of the hostname for SERVER, like I did above.
Feel free to add or delete them to make your SQL Server more compatible.
The next step is to build a cursor object to which you can send a T-SQL command. The cursor() technique is used to do this.
As illustrated below, you now have an object with an execute() function that can be used to feed any T-SQL command into it. The resultant dataset is stored in the rows variable.
cursor.execute(“SELECT * FROM <tablename>”) rows = cursor.fetchall()
You’ve reached the stage where you must select how to proceed with the dataset. You may save the results to a CSV file, import them into another database, or output them to the console.
If the dataset is filled, I’ll report the findings to the console.
Python code for printing SQL rows
When the dataset is printed to the console, the result isn’t very appealing. It’s up to you at this stage to determine how to format or interpret the database data. The difficult part is finished!
You should now have a script that looks like this:
!pyodbc import /usr/bin/python pyodbc.connect = conn (‘DRIVER={FreeTDS};SERVER=server.domain.local;PORT=1433;UID=DOMAINuser;PWD=mypassword;DATABASE=mydatabasename;UseNTLMv2=yes;TDS Version=8.0;Trusted Domain=domain.local;’) cursor.execute(“SELECT * FROM <tablename>”) rows = cursor.fetchall() if rows: print(rows)
Summary
You learnt how to connect to a SQL Server data source on Linux using the pyodbc Python module in this blog article. The most difficult aspect for me was figuring out the ODBC string, but after you’ve done that, everything should go smoothly.
Why not use performance counters after you’ve connected from Linux to see if you can improve performance?
You can connect to Microsoft SQL Server from Linux using the “connect to ms sql server from kali linux” command.
Related Tags
- how to test sql server database connection from linux command line
- connect to sql server from linux shell script
- sqlcmd connect to database
- linux mssql client
- mssql-tools linux
