how to access localDB from Windows Service

Question

I need to use data base locally. I have created database in (LocalDB)\v11.0 Server with Visual Studio 2012. Everything is ok when connecting from e.g. console application. But my application is Windows Service. trying to connect to my data base outputs with:

"... The login failed.Login failed for user 'NT AUTHORITY\SYSTEM"

Is there any solution for this? Maybe I should think of setting up other db server for it? If yes, how to set up it so that it would be visible only locally?

Answer 1

So i found out solution myself, how to connect instance of (localdb) from Windows Service which runs as Local System:

I used this articles: http://technet.microsoft.com/pl-pl/library/hh212961.aspx http://dba.fyicenter.com/faq/sql_server_2/Verifying_a_Login_Name_with_SQLCMD_Tool.html http://social.technet.microsoft.com/wiki/contents/articles/4609.troubleshoot-sql-server-2012-express-localdb.aspx

So i shared my localdb (as in 1st article) the problem was I couldn't connect with sqlcmd named pipe np://. I found anwer in 3rd article:

When using sqlcmd, ensure that you are using the SQL Server 2012 version (found in %Program Files%\Microsoft SQL Server\110\Tools\Binn). If you have previous versions of sqlcmd installed, calling sqlcmd alone from the command line will most likely use the old version (which isn't localdb-aware) since the older path appears first in your PATH environment variable. It may be a good idea, in general, to manually adjust your PATH environment variable so that the 110 versions are picked up first.

This small information in fact was crucial;)

so i created user: user with password: pass321!@.

In my windows service my Sql connectionString looks:

"Data Source=(localdb)\\.\\MyInstanceShared;Integrated Security=false;User Id=user;Password=pass321!@"

Integrated security set to false value is also important.

Maybe it will help somebody.