How to resolve Azure "Windows logins are not supported in this version of SQL Server"?

38
votes

I get the following error message, when I try to connect to SQL Azure.

Windows logins are not supported in this version of SQL Server

I'm using an Azure connection string. On development I'm running against SQL Server Express. This specific error is thrown when I try to fetch some data from the database.

The context that I'm using is running in a using clause, see below

function List<SomeType> GetList(string dbContextName) 
{ 
    using (MyDbContext context = new MyDbContext) 
    {
         return context.SomeTypes.ToList();
    } 
}

We're using Entity Framework version 4.2, ASP.NET MVC 3 and .NET 4.0.

How can I resolve this issue?

6
c#sql-serverasp.net-mvcentity-frameworkazure-sql-database
It seems pretty clear that you need to use a SQL login instead of a Windows login for security - i.e. no SSPI etc; just a SQL username/password – Marc Gravell
SHOW US the connection string(s) used ! You cannot use the Integrated Security=SSPI; setting against SQL Azure, that probably is the default for your local SQL Server Express installation... – marc_s
How on earth do you manually enter a connection string to SSMS? It doesn't have anywhere to paste one. – rollsch

6 Answers

47
votes

I was using user/pass and still got the error message. But, I added this to my connection string and it worked.

Trusted_Connection=False;Encrypt=True;
20
votes

Set

Integrated Security=False

In Connection String.

8
votes

Integrated authentication (i.e. SSPI in the connection string) is NOT supported in SQL Azure. Only SQL Authentication is supported (i.e. username & password in the connection string)

8
votes

You've probably used the incorrect connection string, this is the connection string format that worked for my case:

"ConnectionString": "Server=tcp:xxxx.database.windows.net,1433;Database=xxx;User ID=xxx;Password=xxx;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

8
votes

Az already mentioned by others, only SQL Server authentication is supported in SQL Azure. You can read more on Guidelines and Limitations with SQL Azure. As well as the Security Guidelines and limitations for SQL Azure.

You have to CREATE LOGIN by yourself in your MASTER database, then you will want to CREATE USER in your custom Azure Database. Also do not forget to execute sys.sp_addrolemember to grant some permissions to your user.

More on managing users and logins in SQL Azure can be found here.

And, at the end, you can always look at the invaluable source for connection strings.

5
votes
1.**Windows Authentication** is not supported in Azure so you should go with **SQL Server Authentication**.
2.When you use SQL server Authentication you should pass User Id(Login in SQL server) and Password(Password in SQL server).
3.User Id should contain space in between should not be like UserId.
4.Trusted_Connection should be false.
The connection string in *appsettings.json* look like this:

"ConnectionStrings": {
    "DBContext": "Server=ServerName;Database=DbName;User Id=loginName;Password=loginPassword;Trusted_Connection=false;MultipleActiveResultSets=true"
  }