Synapse / SQL Password so secure that even you cannot use
Published Feb 22 2021 07:23 AM 3,824 Views
Microsoft

 

Teaser card - security.PNG

Everyone knows that the your password should be very strong, and there companies that use password generator to create complex password and this is fine too. But sometimes or better saying in some conditions this can cause some connectivity issues

 

Important note on Security: To keep this article short and focused on the password itself, the sample code below uses passwords in connection string in clear text. This is strongly recommended against in production code. Any sensitive information like passwords should be stored as secrets in the Azure Key Vault.

 

 

Lets start using a free password generator I found on internet where I got something that looks like a good password "q3@yGfAm@JHM".

FonsecaSergio_0-1612824941391.png

 

Let me create a login + user using it TSQL below

 

 

 

 

 

 

--MASTER DB
CREATE LOGIN UserXPTO WITH PASSWORD = 'q3@yGfAm@JHM'

--USER DB
CREATE USER UserXPTO FOR LOGIN UserXPTO

 

 

 

 

 

 

Now let me try to connect to is using .NET SQLClient

 

 

 

 

 

Clear-Host

Write-Host "NATIVE CLIENT TEST" -ForegroundColor Green

$Server = "tcp:xxxxx.sql.azuresynapse.net,1433"
$Database = "dwpool"
$Username = "UserXPTO"
$Password = "q3@yGfAm@JHM"
$SQLText = "Select getdate() as NOW"

########################################################################################
$connectionTimeout = 15

$connectionString = "Server=$Server;"
$connectionString += "Initial Catalog=$Database;"
$connectionString += "User ID=$Username;"
$connectionString += "Password=""$Password"";"
$connectionString += "Connection Timeout=$($connectionTimeout);"
$connectionString += "Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False"

$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

Write-Host "CurrentTime: $(((Get-Date).ToUniversalTime()).ToString("yyyy-MM-dd HH:mm:ss")) UTC"
Write-Host "Connection to Server ($($Server)) / DB ($($Database)) / UserName ($($Username))"

Try {
    $connection.open()
    Write-Host "Connection with success"

    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand
    $command.CommandTimeout = 60
    $command.connection = $connection
    $command.CommandText = $SQLText

    $result = $command.ExecuteScalar()    
    Write-Host "Query success. Server currenttime ($($result))"
    $connection.Close()
}
Catch {
    Write-Error $_.Exception.Message
}

 

 

 

 

 

 

 

 

Results looks fine

NATIVE CLIENT TEST
CurrentTime: 2021-02-08 23:25:29 UTC
Connection to Server (tcp:xxxxx.sql.azuresynapse.net,1433) / DB (dwpool) / UserName (UserXPTO)
Connection with success
Query success. Server currenttime (02/08/2021 23:25:29)

 

Now let's move to ODBC (Current installed version 17.6.1.1) using basically same code

 

 

 

 

 

 

Clear-Host

Write-Host "ODBC CLIENT TEST" -ForegroundColor Green

$Server = "tcp:xxxxx.sql.azuresynapse.net,1433"
$Database = "dwpool"
$Username = "UserXPTO"
$Password = "q3@yGfAm@JHM"
$SQLText = "Select getdate() as NOW"

########################################################################################
$connectionTimeout = 15

$connectionString = "Driver={ODBC Driver 17 for SQL Server};"
$connectionString += "Server=$Server;"
$connectionString += "Database=$Database;"
$connectionString += "UID=$Username;"
$connectionString += "PWD=""$Password"";"
$connectionString += "Connection Timeout=$($connectionTimeout);"
$connectionString += "Encrypt=yes;TrustServerCertificate=no"

$connection = New-Object -TypeName System.Data.Odbc.OdbcConnection($connectionString)

Write-Host "CurrentTime: $(((Get-Date).ToUniversalTime()).ToString("yyyy-MM-dd HH:mm:ss")) UTC"
Write-Host "Connection to Server ($($Server)) / DB ($($Database)) / UserName ($($Username))"

Try {
    $connection.open()
    Write-Host "Connection with success"

    $command = New-Object System.Data.Odbc.OdbcCommand
    $command.CommandTimeout = 60
    $command.connection = $connection
    $command.CommandText = $SQLText

    $result = $command.ExecuteScalar()    
    Write-Host "Query success. Server currenttime ($($result))"
    $connection.Close()
}
Catch {
    Write-Error $_.Exception.Message
}

 

 

 

 

 

 

And now the results

 

ODBC CLIENT TEST
CurrentTime: 2021-02-08 23:26:13 UTC
Connection to Server (tcp:xxxxx.sql.azuresynapse.net,1433) / DB (dwpool) / UserName (UserXPTO)
Write-Error: Exception calling "Open" with "0" argument(s): "
ERROR [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'UserXPTO'.

ERROR [01S00] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

If we looks at first error "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'UserXPTO'." we will start believe that there is an issue with login + password.

 

And sometimes it can really be. Better double check the password. Or test to connect using different tool like Azure SQL Data Studio or SQLCMD

 

Looking into ODBC connection string it looks ok too

Driver={ODBC Driver 17 for SQL Server};Server=tcp:xxxxx.sql.azuresynapse.net,1433;Database=dwpool;UID=UserXPTO;PWD="q3@yGfAm@JHM";Connection Timeout=15;Encrypt=yes;TrustServerCertificate=no

There is no difference in the code

FonsecaSergio_0-1612909406743.png

 

There is also this sample from a customer case I worked recently where cx was also getting some connection error. But error message was little bit different. But It looks like just permission problem or (user + password) error

FonsecaSergio_1-1612827099816.png

 

This one on my environment went fine

FonsecaSergio_2-1612910256676.png

 

 

I could not repro customer exact error probably because of version he had vs the version I was using but the error behind the scene is same for both and its explained at https://docs.microsoft.com/en-us/sql/relational-databases/security/strong-passwords

 

Passwords can be the weakest link in a server security deployment. Take great care when you select a password. A strong password has the following characteristics:
- Is at least eight characters long.
- Combines letters, numbers, and symbol characters within the password.
- Is not found in a dictionary.
- Is not the name of a command.
- Is not the name of a person.
- Is not the name of a user.
- Is not the name of a computer.
- Is changed regularly.
- Is different from previous passwords.

Microsoft SQL Server passwords can contain up to 128 characters, including letters, symbols, and digits. Because logins, user names, roles, and passwords are frequently used in Transact-SQL statements, certain symbols must be enclosed by double quotation marks (") or square brackets ([ ]). Use these delimiters in Transact-SQL statements when the SQL Server login, user, role, or password has the following characteristics:
- Contains or starts with a space character.
- Starts with the $ or @ character.

If used in an OLE DB or ODBC connection string, a login or password must not contain the following
characters: [] () , ; ? * ! @ =. These characters are used to either initialize a connection or
separate connection values.

 

You can use some workarounds / solutions below

  • If you cannot change application code and getting this error, just DO NOT use these special char documented above.

 

 

  • If you can: try to change how you set the password. On my tests I was able to work around the issue by not using quotes in the password (xxx vs "xxxvs 'xxx'), but it will probably depend on the char used
    • $connectionString += "PWD=""$Password"";" - FAILS
    • $connectionString += "PWD='$Password';" - FAILS
    • $connectionString += "PWD=$Password;" - OK

Quotes are supported, but for some reason help to prevent the error https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722656(v=vs.85)#setting-values-...

 

Co-Authors
Version history
Last update:
‎Sep 15 2021 12:06 PM
Updated by: