2 processes accessing one mdf file

[Darwin]

Hi,
VS2005
I am trying to write a service and a service controller. Independently, both
work properly. They both access an mdf file with the following connection
string:
String connectionString = @"Data Source=.\SQLEXPRESS;" +

"Initial Catalog=;" +

@"AttachDbFilename=C:\development\portSAF\portSAF.mdf;" +

"Integrated Security=True;Connect Timeout=30;User Instance=True;";

It appears that when any other process (VS included) connects to the mdf,
the service can no longer access the database and generates the following
error:
Connection to database failed System.Data.SqlClient.SqlException: Cannot
open user default database. Login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SAFservice.SAFserverService.GetData(String selectCommand) in
C:\development\SAF\SAFService\SAFserverService.cs:line 291

But, if the processes are run separately (synchronously) the data can be
accessed. It looks like the access to the database is limited to one
connection per login?? That doesn't seem correct via the docs.

Any help is appreciated.

[Andrea Montanari]

hi,

Post by Darwin
Hi,
VS2005
I am trying to write a service and a service controller.
Independently, both work properly. They both access an mdf file with
"Initial Catalog=;" +
@"AttachDbFilename=C:\development\portSAF\portSAF.mdf;" +
"Integrated Security=True;Connect Timeout=30;User Instance=True;";
It appears that when any other process (VS included) connects to the
mdf, the service can no longer access the database and generates the
Cannot open user default database. Login failed.

try having a look at
http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic7

[Darwin]

Yes, that did it.
Had to "sp_detach_db" because the windows service runs as a different user
to the service controller.

Learnt a lot thanks.

Post by Andrea Montanari
hi,

Post by Darwin
Hi,
VS2005
I am trying to write a service and a service controller.
Independently, both work properly. They both access an mdf file with
"Initial Catalog=;" +
@"AttachDbFilename=C:\development\portSAF\portSAF.mdf;" +
"Integrated Security=True;Connect Timeout=30;User Instance=True;";
It appears that when any other process (VS included) connects to the
mdf, the service can no longer access the database and generates the
Cannot open user default database. Login failed.

try having a look at
http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic7
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply