0

I'm using below connection string to connect AZURE SQL server, however We see below error. Please advice

Connection string: Data Source=servername.database.windows.net;Encrypt=True;Initial Catalog=dbname; Authentication=Active Directory Service Principal; User Id=userid; Password=password;

Activity done: Added the respective service principal is AZURE SQL server Ran below queries

DB: target DB -> CREATE USER [MyServicePrincipleName] FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [MyServicePrincipleName];
EXEC sp_addrolemember N'db_datareader', N'MyServicePrincipleName';
EXEC sp_addrolemember N'db_datawriter', N'MyServicePrincipleName';
ALTER ROLE db_datareader ADD MEMBER [MyServicePrincipleName];
ALTER ROLE db_datawriter ADD MEMBER [MyServicePrincipleName];
GRANT EXECUTE TO [MyServicePrincipleName];
GRANT UNMASK TO [MyServicePrincipleName];

Error message:
System.Data.Entity.Core.EntityException
HResult=0x80131501
Message=The underlying provider failed on Open.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.b__0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable.GetEnumerator>b__31_0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() This exception was originally thrown at this call stack:

[External Code]
Inner Exception 1:
SqlException: Login failed for user '<token-identified principal>'.
Vigneash Selvaraj
  • 57
  • 1
  • 5

1 Answers1

0

SqlException: Login failed for user ''.

This typically means that the service principal has not been added to the database. Note only this

CREATE USER [MyServicePrincipleName] FROM EXTERNAL PROVIDER;

is required to connect.

You may be connecting to the wrong database, or you added the user to the wrong database. It's also possible that you have two service principals with the same name, and you're connecting with the AppId of the wrong one.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67