CODEWITHSUNDEEP
c#sql.netsql-server
santBart
santBart

Reputation: 2496

how to access localDB from Windows Service

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?

Upvotes: 5

Views: 8392

Answers (3)

santBart
santBart

Reputation: 2496

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.

Upvotes: 12

Jportelas
Jportelas

Reputation: 646

Probably your connection string is using the current Windows User account (the account that runs the service could be the one seen on the error) to connect to the database (and that user account does not have access). What I would do is that I would change the database to allow "mixed authentication" (this means SQL users and Windows users too) and then I would create a new user, assign that user the required permissions to work with your app and then use that userName and password in the connection string from your windows service. You could also leave the DB as it is and add user "System" from Windows and then assign the required permissions to it. You can just add "dbo" profile to a user to make it work fast (but check security later before releasing your product).

Upvotes: 1

Clement Dungler
Clement Dungler

Reputation: 747

I think that you need to allow remote connection to your SqlServer.

In the Sql Server configuration manager, under SQL Network Configuration, chose your server and check that TCP/IP is enabled.

In my case, doublic click on TCP/IP, go to the bottom "IPAll" and set dynamic ports empty and TCP Port to 1433.

Let me know if it's enough.

Upvotes: -3

Related Questions