February 27, 2011 at 2:40 am
Hi,
I am getting the following connection error when trying to connect to my Express database, this worked prior to upgrading from SQL 2008 Express.
Error connecting to database: An attempt to attach an auto-named database for file C:\Users\Peter Annandale\Documents\Visual Studio 2010\Projects\DVMSystemWinSqlite\DVMSystemWin\bin\Debug\Data\DVMSData1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have tried all the normal posted resolutions and none seem to fix the problem, the file exists in the specified location. I added seciurtiy to the directory to ensure SQLExpress user had rights to the directory and still no go.
Can anyone please advise a solution.
Regards..
Peter 🙁
February 27, 2011 at 7:57 pm
Trying to connect? You mean from SSMS, choosing Connect and then Database Engine and specifying the R2 engine running some where? I can't imagine this is the case since the error doesn't seem to relate to establishing a connection to an already running instance on some computer.
It sounds like you are trying to Attach a database and receiving this error. If so, where is the matching .ldf file? Try using code to attach (ATTACH DATABASE...) to specify an mdf and ldf not already attached and provide a db name which is not already used on is system. See BOL for more details on the syntax.
We need more details of exactly what you are trying to do and what you have already done, otherwise, we are guessing.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 27, 2011 at 8:11 pm
Hi Jim,
Thanks for the response, it would appear that the error message was somewhat misleading. I managed to resolve the problem today by changing the login that SQL-Express runs under.
It was originally running under "Network Service", once I changed to to "Local System" the application could now connect to it's database.
Regards..
Peter
February 27, 2011 at 9:32 pm
Ah, OK. Glad it's resolved.
Sounds like it was a permissions issue with that account not having permissions to the data files.
Local system is a very high priv account and opens the way for security issues. A big way. It is recommended to use a local user or domain user account to limit the risk exposure. Course, this get into more permissions issues and settings. There is much written on how to configure a lower priv account to have the necessary access to the files, registry, etc.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 28, 2011 at 2:19 am
Hi Jim,
I did try and give the SQL-Express User the correct rights to the folder and then the file but this was unsuccessful. The database is used for a local application so my concern over security wasn't too high.
Regards..
Peter.
February 28, 2011 at 8:31 am
Hey Peter,
There is more info about setting up a lower priv account to run SQL server here: http://msdn.microsoft.com/en-us/library/ms143504.aspx.
See this for the security risk: http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/11/13/why-we-recommend-against-xp-cmdshell.aspx
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 28, 2011 at 2:37 pm
Hi Jim,
Thanks for the updated information, I tried changing the account used for SQLExpress to Local Service and Network Service, both of these account will not allow me to connect to the database even after giving the appropriate rights to these accounts to the folder containing the SQLExpress database I am trying to connect to when the application runs.
I get the same error as previously posted.
Regards..
Peter
February 28, 2011 at 4:00 pm
Peter - there is much more too it than file system permissions. Please refer to the links in my previous post for more information.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 28, 2011 at 4:38 pm
Hi Jim,
I couldn't get access to the MSDN article this morning, I will review later today.
Thanks
Peter.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply