search

markrendle / Simple.Data

A light-weight, dynamic data access component for C# 4.0
MIT License
1.33k stars 303 forks source link

Login failed when using Azure SQL ConnectionString #263

Closed hoetz closed 11 years ago

hoetz commented 11 years ago

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.

markrendle commented 11 years ago

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 11 years ago

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";

markrendle commented 11 years ago

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 11 years ago

"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 11 years ago

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)

markrendle commented 11 years ago

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 11 years ago

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 :)

ermukeshkumar commented 9 years ago

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

jdscolam commented 9 years ago

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