search

aws-samples / aws-secrets-manager-rotation-lambdas

Contains Lambda functions to be used for automatic rotation of secrets stored in AWS Secrets Manager
MIT No Attribution
329 stars 282 forks source link

Lambda for sql database rotation does not work as expected #149

Closed mobious999 closed 1 month ago

mobious999 commented 1 month ago

Hi,

Pymssql has been deprecated and no longer functions when attempting to rotate passwords.

https://docs.aws.amazon.com/secretsmanager/latest/userguide/rotating-secrets-required-permissions-function.html

The Microsoft stance is that you should be using pyodbc. https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver16

Also there is no guide on how to get the lambda to rotation to work with ssl connections to the database.

jirkafajfr commented 1 month ago

Hi Mark,

would you mind sharing more information with us? We're interested about your:

We are running integration tests against rotation functions, and don't observe anything out of ordinary. Microsoft still advertises pymssql as supported driver.

jirkafajfr commented 1 month ago

Also there is no guide on how to get the lambda to rotation to work with ssl connections to the database.

Most of the rotation lambdas try to connect with SSL by default. When the connection is unsuccessful they'll fallback to the plaintext. You can modify this behavior by providing the ssl key in the secret, following logic is then used.

mobious999 commented 1 month ago

Hi @jirkafajfr,

They python version is 3.11, and you can build the layer to whatever version you need.

The sql server version is 17 I believe, and pymssql failed with the ssl connections originally and it's undocumented.

I stopped using the pymssql lambda function after the second set of failures because of the ssl issues (it won't connect to the database, and there are no options that require ssl set).

As you can see - https://github.com/pymssql/pymssql/issues?page=1&q=is%3Aissue+is%3Aopen the project has 150 open issues and it is not actively being worked on.

As compared to pyodbc - https://github.com/mkleehammer/pyodbc/issues 35 known issues and is actively being worked on.

I'm glad you can get the pymssql working but I was forced to give up due to time constraints.

jirkafajfr commented 1 month ago

Hi Mark,

the latest sql server is version 16 (reference). When you say 17 do you mean 2017? I can verify with our integration tests that the library is really not a problem - but I need to know the exact versions - right now we don't see any failures in our coverage.

I agree that pyodbc is newer and more maintained version. But as pointed out in previous post pymssql is still advertised as valid driver on the Microsoft site. We have no plans to migrate away from pymsql unless Microsoft declares it as not recommended solution or if there is a compatibility problem.

Please feel free to share exact SSL problems you're experiencing. But unless we can reproduce on our side, we are not willing to migrate away from current driver.

I also wanted to let you know that these lambdas are reference implementation for customers that want to customize the code. Using one of the following two deployment options is typically much easier and you don't have to worry about exact python / driver versions:

Service Application Repository

CloudFormation - Transform

https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/transform-aws-secretsmanager.html