Credentials using Invoke-Sqlcmd against sql azure

Question

I'm attempting to run a powershell build script against a sql azure database but receiving Login failed for user 'X'.

I'm fairly convinced the credentials are correct as they were taken straight from the live application config.

This is the command I'm using:

Invoke-Sqlcmd -InputFile "Build.sql" -ServerInstance $server -Database $database `
 -WarningAction SilentlyContinue -OutputSqlErrors $false `
 -Username $username -Password $password -EncryptConnection

I had this working with sqlcmd in a batch file so I'm wondering if it's got anything to do with the way the credentials are being sent, trusted_connection=false doesn't appear to be an option I can try.

Answer 1

It could be the password contains a few special characters that Azure/Invoke-sqlcmd does not handle (such as dollar, single or double quote, parentheses). I tried using the Azure interface and surrounding the password with single-quotes (we had a dollar-sign in the password), but that did not work. So, we simply removed the special character and now it is OK. see: Powershell Invoke-Sqlcmd Login Failed and https://mohitgoyal.co/2017/08/09/vsts-azure-sql-database-deployment-task-keeps-failing-with-error-login-failed-for-user/

Answer 2

When connecting to SQL Azure the login name must be of the form user@server. So if you created an user 'foo' and a server 'bar', the login must be foo@bar. See Managing Databases and Logins in Azure SQL Database:

Because some tools implement tabular data stream (TDS) differently, you may need to append the Azure SQL Database server name to the login in the connection string using the <login>@<server> notation. In these cases, separate the login and Azure SQL Database server name with the @ symbol. For example, if your login was named login1 and the fully qualified name of your Azure SQL Database server is servername.database.windows.net, the username parameter of your connection string should be: login1@servername.

CREATE LOGIN also explains this:

In some methods of connecting to SQL Database, such as sqlcmd, you must append the SQL Database server name to the login name in the connection string by using the <login>@<server> notation. For example, if your login is login1 and the fully qualified name of the SQL Database server is servername.database.windows.net, the username parameter of the connection string should be login1@servername.

• Phil Cooper: Thanks for the tip and url, although I've triple checked that. I think I might have to try and roll a different command to see if it's that. Something tells me its the authentication mode but example on the net suggest I'm doing it correctly. I even logged into to the online management with the same details :(