5

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?

santBart
  • 2,466
  • 9
  • 43
  • 66

3 Answers3

12

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.

santBart
  • 2,466
  • 9
  • 43
  • 66
  • To avoid confusion caused by additional / for C# string escaping the connection string should look like this (with single `\\`): Data Source=(localdb)\.\MyInstanceShared;Integrated Security=false;User Id=user;Password=pass321!@ – Dmitry Pavlov May 19 '17 at 13:23
1

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).

Jportelas
  • 646
  • 10
  • 21
  • Can you possibly tell me how to manage users from Visual Studio? – santBart Feb 17 '14 at 18:28
  • Yo should have SQL Server management studio probably, in fact how did you create the database you're using?. I don't know if you can manage security from Visual Studio but from SQL management studio I am sure you can. Just connect to your local server using management studio, then open the security tab/folder and add the "SYSTEM" user to your database. Check these links, they might help: http://stackoverflow.com/questions/360141/how-to-connect-to-local-instance-of-sql-server-2008-express and http://msdn.microsoft.com/en-us/evalcenter/hh230763 – Jportelas Feb 17 '14 at 21:18
-3

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.

Clement Dungler
  • 737
  • 6
  • 10
  • But I don't want to enable remote connection to db. – santBart Feb 17 '14 at 14:03
  • A windows service use the tcp/ip protocol to connect to the DB. Even if you are on the same machine.. – Clement Dungler Feb 17 '14 at 14:09
  • Not sure but when you start your web service, it's using the ASP.Net developer server which is not considered as "local" for your SQL Server. Did it works for you now ? – Clement Dungler Feb 18 '14 at 09:57
  • Its windows service not web service. I hvae read that it isnt good practice to enable txp on express localdb – santBart Feb 18 '14 at 10:03
  • But have you test this settings ? I use 3 types of application : WPF, Windows Service, Web Service. All use the same classes for the connection but it won't work if TCP/IP is not enabled. I use SQL Server instead of Express. – Clement Dungler Feb 18 '14 at 10:13