CODEWITHSUNDEEP
powershellazure-sql-database
Phil Cooper
Phil Cooper

Reputation: 3123

Credentials using Invoke-Sqlcmd against sql azure

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.

Upvotes: 4

Views: 4559

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 2

Davemundo
Davemundo

Reputation: 889

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/

Upvotes: 2

Related Questions