Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Login failed when using Azure SQL ConnectionString #263

Closed
hoetz opened this Issue Mar 14, 2013 · 9 comments

Comments

Projects
None yet
4 participants

hoetz commented Mar 14, 2013

I tried to use the library with a Azure SQL database. When calling db.OpenConnection with this connection string "Server=tcp:xxxxxx.database.windows.net,1433;Database=[xxxxxxxxx];User ID=user@xxxxxxxx;Password=xxxxxxxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;
the login always fails. When using the connection string with standard ADO.NET it works as expected. Since Azure SQL is just a standard SQL Server database it should work.

Owner

markrendle commented Mar 14, 2013

Have you added the IP address you are connecting from to the Azure SQL Database's firewall rules? That's usually the cause of login failures.

Simple.Data definitely works with Azure SQL Database; the CI server is hosted in Azure and runs the SQL Server integration tests against an Azure SQL Database instance.

hoetz commented Mar 14, 2013

Yes we have added the IP. We are able to connect to the database via SQL Management Studio on the same machine. We can also connect to the DB via ADO.NET utilizing this ConString Builder on the same machine:

SqlConnectionStringBuilder csBuilder;
csBuilder = new SqlConnectionStringBuilder();
csBuilder.DataSource = "xxxxxx.database.windows.net";
csBuilder.InitialCatalog = "xxxxxx";
csBuilder.Encrypt = true;
csBuilder.TrustServerCertificate = false;
csBuilder.UserID = "user@xxxxx";
csBuilder.Password = "xxxxxx";

Owner

markrendle commented Mar 14, 2013

What error are you getting when Simple.Data tries to connect?

On Thu, Mar 14, 2013 at 2:34 PM, fhoetzing notifications@github.com wrote:

Yes we have added the IP. We are able to connect to the database via SQL
Management Studio on the same machine. We can also connect to the DB via
ADO.NET utilizing this ConString Builder on the same machine:

SqlConnectionStringBuilder csBuilder;
csBuilder = new SqlConnectionStringBuilder();
csBuilder.DataSource = "xxxxxx.database.windows.net";
csBuilder.InitialCatalog = "xxxxxx";
csBuilder.Encrypt = true;
csBuilder.TrustServerCertificate = false;
csBuilder.UserID = "user@xxxxx";
csBuilder.Password = "xxxxxx";


Reply to this email directly or view it on GitHubhttps://github.com/markrendle/Simple.Data/issues/263#issuecomment-14905416
.

hoetz commented Mar 14, 2013

"Cannot open database "[xxxxxxx]" requested by the login. The login failed.\r\nLogin failed for user 'user'.\r\nThis session has been assigned a tracing ID of '5ef7a8ed-acae-4ea7-b487-e6e9bd09cxxx'. Provide this tracing ID to customer support when you need assistance."

hoetz commented Mar 14, 2013

Stacktrace:
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) bei System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) bei System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) bei System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) bei System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) bei System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) bei System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) bei System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) bei System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) bei System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) bei System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) bei System.Data.SqlClient.SqlConnection.Open() bei Simple.Data.SqlServer.SqlSchemaProvider.GetSchema(String collectionName, String[] constraints) bei Simple.Data.SqlServer.SqlSchemaProvider.GetTables() bei Simple.Data.Ado.Schema.DatabaseSchema.CreateTableCollection() bei System.Lazy1.CreateValue()
bei System.Lazy1.LazyInitValue() bei System.Lazy1.get_Value()
bei Simple.Data.Ado.Schema.DatabaseSchema.FindTable(ObjectName tableName)
bei Simple.Data.Ado.FindHelper.GetSelectClause(ObjectName tableName)
bei Simple.Data.Ado.FindHelper.GetFindByCommand(ObjectName tableName, SimpleExpression criteria)
bei Simple.Data.Ado.AdoAdapterFinder.CreateFindOneDelegate(String tableName, SimpleExpression criteria)
bei Simple.Data.Ado.AdoOptimizingDelegateFactory.CreateFindOneDelegate(Adapter adapter, String tableName, SimpleExpression criteria)
bei Simple.Data.Commands.FindByCommand.CreateDelegate(DataStrategy dataStrategy, DynamicTable table, InvokeMemberBinder binder, Object[] args)
bei Simple.Data.DynamicTable.CreateMemberDelegate(String signature, InvokeMemberBinder binder, Object[] args)
bei Simple.Data.DynamicTable.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
bei Simple.Data.ObjectReference.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
bei CallSite.Target(Closure , CallSite , Object , Int32 )
bei System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)

Owner

markrendle commented Mar 14, 2013

Try taking the @blahblah off the user ID. I don't have it in the connection
string used by the CI server and it works OK.

On Thu, Mar 14, 2013 at 2:59 PM, Florian Hötzinger <notifications@github.com

wrote:

Stacktrace:
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection, Action1 wrapCloseInAction)
bei
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK)
bei
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
bei
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance, SqlConnectionString connectionOptions,
SqlCredential credential, TimeoutTimer timeout)
bei
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer
timeout, SqlConnectionString connectionOptions, SqlCredential credential,
String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance)
bei
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, SqlCredential credential,
Object providerInfo, String newPassword, SecureString newSecurePassword,
Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions)
bei
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
bei
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool
pool, DbConnectionOptions options, DbConnectionPoolKey poolKey,
DbConnectionOptions userOptions)
bei
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions
userOptions)
bei
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions
userOptions)
bei
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate,
Boolean onlyOneCheckConnection, DbConnectionOptions userOptions,
DbConnectionInternal& connection)
bei
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, TaskCompletionSource1 retry, DbConnectionOptions
userOptions, DbConnectionInternal& connection)
bei
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource1 retry, DbConnectionOptions
userOptions, DbConnectionInternal& connection)
bei
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1
retry, DbConnectionOptions userOptions)
bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1
retry)
bei System.Data.SqlClient.SqlConnection.Open()
bei Simple.Data.SqlServer.SqlSchemaProvider.GetSchema(String
collectionName, String[] constraints)
bei Simple.Data.SqlServer.SqlSchemaProvider.GetTables()
bei Simple.Data.Ado.Schema.DatabaseSchema.CreateTableCollection()
bei System.Lazy1.CreateValue()
bei System.Lazy1.LazyInitValue()
bei System.Lazy1.get_Value()
bei Simple.Data.Ado.Schema.DatabaseSchema.FindTable(ObjectName tableName)
bei Simple.Data.Ado.FindHelper.GetSelectClause(ObjectName tableName)
bei Simple.Data.Ado.FindHelper.GetFindByCommand(ObjectName tableName,
SimpleExpression criteria)
bei Simple.Data.Ado.AdoAdapterFinder.CreateFindOneDelegate(String
tableName, SimpleExpression criteria)
bei
Simple.Data.Ado.AdoOptimizingDelegateFactory.CreateFindOneDelegate(Adapter
adapter, String tableName, SimpleExpression criteria)
bei Simple.Data.Commands.FindByCommand.CreateDelegate(DataStrategy
dataStrategy, DynamicTable table, InvokeMemberBinder binder, Object[] args)
bei Simple.Data.DynamicTable.CreateMemberDelegate(String signature,
InvokeMemberBinder binder, Object[] args)
bei Simple.Data.DynamicTable.TryInvokeMember(InvokeMemberBinder binder,
Object[] args, Object& result)
bei Simple.Data.ObjectReference.TryInvokeMember(InvokeMemberBinder binder,
Object[] args, Object& result)
bei CallSite.Target(Closure , CallSite , Object , Int32 )
bei System.Dynamic.UpdateDelegates.UpdateAndExecute2T0,T1,TRethttp://CallSite%20site,%20T0%20arg0,%20T1%20arg1


Reply to this email directly or view it on GitHubhttps://github.com/markrendle/Simple.Data/issues/263#issuecomment-14906927
.

hoetz commented Mar 15, 2013

Yep we tried that :(
Edit: Oh my god, it works when removing the brackets from the database name! The @blabla in the username is ok actually! Sorry for bothering you. Maybe it helps someone in the future :)

@markrendle markrendle closed this May 21, 2013

I ran into the same problem. @hoetz I don't know what're you talking about the brackets.
Anyway I'm developing ASP.NET Web APIs with SQL Azure as back end. I'm getting following exception "Login failed for user 'MyDatabaseUserName'. This session has been assigned a tracing ID of '9d6de4e6-bb66-4f36-8cab-8XXXXXXXXXXX'. Provide this tracing ID to customer support when you need assistance".
My Web API working fine on localhost while database connected as SQL Azure. But when I host it onto Azure Website. I get that exception. Weird right? I don't understand why's this happening? If anybody can help?
TIA

Contributor

jdscolam commented Dec 2, 2014

@ermukeshkumar make sure you are using the right fully qualified username. It usually looks like this: MyDatabaseUserName@blahblahblah

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment