I have an Azure SQL Database setup and working. When I set up the SQL Server instance, I selected Windows Azure authentication. I can authenticate with my Windows AD account. I would like to use SQL Authentication as well as Windows Azure (Mixed Mode). How do I change the base authentication options on the Azure SQL Database? Thank you. Jim

By the default, both authentications can be used if you don't have checked the check button related to "Support only Azure Active Directory authentication for this user". Only when you set that on, you will only be able to authenticate using Azure AD. When you set the AD Admin you can go and change your SQL Admin password. So you are having both if the check button is clear, This is configured at the Azure SQL logical server level.

Azure SQL - Change to Mixed Authentication Mode

Accepted answer

Alberto Morillo 33,426 Reputation points MVP

2022-03-21T20:46:23.423+00:00

By the default, both authentications can be used if you don't have checked the check button related to "Support only Azure Active Directory authentication for this user". Only when you set that on, you will only be able to authenticate using Azure AD.

When you set the AD Admin you can go and change your SQL Admin password. So you are having both if the check button is clear,

This is configured at the Azure SQL logical server level.
Please sign in to rate this answer.

1 person found this answer helpful.
Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2022-03-25T17:07:16.477+00:00

@James Lux Please mark as accept answer if the above answer provided by @Alberto Morillo was helpful.

Regards,
Oury

Jeff Bowman 101 Reputation points

2022-04-18T21:05:41.057+00:00

Hi Alberto

My checkbox is already unselected, yet this command (osql -E -S some.database.windows.net) shows my Authentication option as WindowsIntegrated. (My base problem is here.)

Do you know why this is the case?

Thanks,
Jeff Bowman
Fairbanks, Alaska

Alberto Morillo 33,426 Reputation points MVP

2022-04-18T21:32:51.94+00:00

@Jeff Bowman Jeff please use sqlcmd as osql is a deprecated feature.

You can use it like this:

$params = @{ 'Database' = 'myazuredatabase' 'ServerInstance' = 'myServer.database.windows.net' 'Username' = 'sqladmin' 'Password' = 'password' 'OutputSqlErrors' = $true 'Query' = 'CREATE DATABASE db_copy AS COPY OF ozabzw7545.db_original ' } Invoke-Sqlcmd @params

Another way to use it:

sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -U myuser -P myP@ssword

Don't use it from inside SQL Server Management Studio as it always get connected to the master database.

Jeff Bowman 101 Reputation points

2022-04-19T01:30:55.313+00:00

Thank you, and your SqlCmd version works, but my point is about the conflicting information. The Azure portal says Mixed Mode is on, but the osql command says it's not.

Do you have any theories as to why this is the case?

Further, do you have any ideas about a solution to my base problem (link here)?

Jeff Bowman 101 Reputation points

2022-04-19T01:34:57.773+00:00

Come to think of it... if the SqlCmd version works, then Mixed Mode must be on. That'd be pretty much obvious.

Not sure why the response to the osql command indicates WindowsIntegrated. Strange.

Alberto Morillo 33,426 Reputation points MVP

2022-04-19T10:19:33.677+00:00

Please specify the user and password:

sqlcmd -S xxxxxxx.database.windows.net -U loginname -P "Password"

Jeff Bowman 101 Reputation points

2022-04-19T10:25:27.97+00:00

Yes, I tried that, and it works, thank you, but my point is about the conflicting information. The Azure portal says Mixed Mode is on, but the response to the osql command says it's not.

Do you have any theories as to why this is the case?

Further, do you have any ideas about a solution to my base problem (link here)?

Alberto Morillo 33,426 Reputation points MVP

2022-04-19T11:14:42.693+00:00

On the screenshotyou are not providing the -U switch. I think that is the reason.

Jeff Bowman 101 Reputation points

2022-04-19T11:51:40.46+00:00

OK, thank you.

Alberto Morillo 33,426 Reputation points MVP

2022-04-19T13:34:02.73+00:00

You are always welcome. Thank you for visiting Microsoft QA today!

Shelby Rothman 0 Reputation points

2023-11-03T22:17:18.88+00:00

What to do if Azure Active Directory is not in the setting panel?

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-12-20T00:04:58.11+00:00

Shelby Rothman

Which setting panel are you referring to.? Could you please share a screen shot.

Regards,

Oury

Jan-Jaap Spies 0 Reputation points

2024-01-06T16:14:19.8933333+00:00

@Shelby Rothman

I suppose you are confused by the fact that AAD (Azure Active Directory) is now rebranded as Microsoft Entra ID. So look for that in the settings of SQL server. See https://devblogs.microsoft.com/identity/aad-rebrand/

@Oury Ba-MSFT It seems to me the question as stated is clear enough?
Sign in to comment

Share via

Azure SQL - Change to Mixed Authentication Mode

0 additional answers