2

I am using a Python script to connect to a SQL Server database:

import pyodbc
import pandas 

server = 'SQL' 
database = 'DB_TEST' 
username = 'USER' 
password = 'My password' 

sql='''
SELECT *
FROM [DB_TEST].[dbo].[test]
'''
cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

data = pandas.read_sql(sql,cnxn)
cnxn.close()

The script is launched everyday by an automatisation tools so there is no physical user.

The issue is how to replace the password field by a secure method?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Catapultaa
  • 170
  • 3
  • 13
  • 2
    What do you mean by "replace the password field by a secure method" ? – nakE Jan 06 '20 at 14:13
  • you can also have the password be an environment variable and read that using `os.environ.get()` – gold_cy Jan 06 '20 at 14:18
  • I mean by a way to prevent from having a clear password in the script. – Catapultaa Jan 06 '20 at 14:21
  • Does this answer your question? [I need to securely store a username and password in Python, what are my options?](https://stackoverflow.com/questions/7014953/i-need-to-securely-store-a-username-and-password-in-python-what-are-my-options) – Jacob H Jan 06 '20 at 14:37
  • Specifically, this answer: https://stackoverflow.com/a/53027302/7948962 – Jacob H Jan 06 '20 at 14:41

2 Answers2

0

The automated script is still ran by a windows user. Add this windows user to the SQL-Server users and give it the appropriate permissions, so you can use:

import pyodbc
import pandas 

server = 'SQL' 
database = 'DB_TEST'

sql='''
SELECT *
FROM [DB_TEST].[dbo].[test]
'''
cnxn = pyodbc.connect(
    f'DRIVER=SQL Server;SERVER={server};DATABASE={database};Trusted_Connection=True;')

data = pandas.read_sql(sql,cnxn)
cnxn.close()
tituszban
  • 4,797
  • 2
  • 19
  • 30
  • I have a login failed for user and I am not allowed to add a specific user as trusted connection – Catapultaa Jan 06 '20 at 14:19
  • If you can't add your user, you will have to use a password. You can put it to places other than your code, but there always will be a vulnerability. (file, env variable, etc) – tituszban Jan 06 '20 at 14:22
  • Yes that what I thought too there is no a strong solution. – Catapultaa Jan 06 '20 at 14:24
0

I am also interested in secure coding using Python .I did my own research to figure out available options, I would recommend reviewing this post as it summarize it all. Check on the listed options, and apply the one suits you better.

Rola
  • 1,598
  • 13
  • 12