Cannot connect to an instance of SQL Server 2008

Question

I am trying to connect to a SQL Server 2008 server instance via NHibernate using C#. I am unable to do so.

I have been successful in connecting to to the same instance via Hibernate using java. So i am kinda sure that there is nothing wrong with my SQL Server 2008 configuration settings. I have also managed to connect to SQL Server 2008 Express.

Here is the hibernate.cfg.xml

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>

    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">server=(local)\SUNIL,3067;Initial Catalog=NHibernate101;User ID=sunil;Password=mypassword;</property>
    <property name="show_sql">true</property>
    <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
    <property name="cache.use_query_cache">false</property>
    <property name="adonet.batch_size">100</property>
    <property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
    <mapping assembly="Infrastructure" />
  </session-factory>
</hibernate-configuration>

Here is the error message:

Test method NHibernate101.Tests.RepositoriesTest.CanCreatePost threw exception: System.Data.SqlClient.SqlException: Login failed for user 'sunil'.

Here is the stack trace:

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) System.Data.SqlClient.SqlConnection.Open() NHibernate.Connection.DriverConnectionProvider.GetConnection() NHibernate.Tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.Prepare() NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper) NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.Update(ISessionFactory sessionFactory) NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners) NHibernate.Cfg.Configuration.BuildSessionFactory() Core.Domain.Repositories.NHibernateHelper.get_SessionFactory() in D:\dotnet\tutorials\NHibernate101\Core\Domain\Repositories\NHibernateHelper.cs: line 22 Core.Domain.Repositories.NHibernateHelper.OpenSession() in D:\dotnet\tutorials\NHibernate101\Core\Domain\Repositories\NHibernateHelper.cs: line 30 Core.Domain.Repositories.CategoryRepository.Core.IRepository.Save(Category entity) in D:\dotnet\tutorials\NHibernate101\Core\Domain\Repositories\CategoryRepository.cs: line 17 NHibernate101.Tests.RepositoriesTest.CanCreatePost() in D:\DOTNet\NHibernate101\NHibernate101.Tests\RepositoriesTest.cs: line 69

Answer 1

The error message seems pretty obvious:

System.Data.SqlClient.SqlException: Login failed for user 'sunil'.

That would indicate that the user specified in the connection string

• has no permission to even connect to the server
• has no permission to connect to the database specified in the connection string
• a wrong password has been specified in the connection string

So I would try to fix your connection string to make sure you can log in:

server=(local)\SUNIL,3067;Initial Catalog=NHibernate101;
   User ID=sunil;Password=mypassword;

• is your SQL Server instance really called SUNIL and resides on your local machine?
• does your SQL Server really use port 3067 ? (this is not the default)
• is your database really called NHibernate101 ?
• does a login called sunil exist on that server?
• is that login's password really mypassword?
• is that user not "blocked" or locked out for some reason?
• does a user for sunil exist in NHibernate101 ?

Can you connect to that server, that database, with exactly that login and password, from SQL Server Management Studio??