Synapse / SQL Password so secure that even you cannot use

by | Feb 22, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

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


 


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 workaround issue not using quotes in the password (xxx vs “xxx” vs ‘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 workaround the error https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722656(v=vs.85)#setting-values-that-use-reserved-characters


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment