I'm writing a windows service that needs to connect to a SQL server to retrieve some information. However, the server doesn't allow me to connect: login failed for user [domain/username]. The strange part is that everything works totally fine from inside SSMS. I can log in, query the database, and everything works out perfectly. It's only through the windows service that it's throwing that error.
我正在编写一个需要连接到SQL服务器以检索某些信息的Windows服务。但是,服务器不允许我连接:用户[域/用户名]登录失败。奇怪的是,SSMS内部的一切都运行得很好。我可以登录,查询数据库,一切都很完美。只有通过Windows服务才会抛出该错误。
For reference, here's the connection string I'm using:
作为参考,这是我正在使用的连接字符串:
Data Source=sqlserveraddresshere;Initial Catalog=databasenamehere;User ID=domain\username;Password=password`
Data Source = sqlserveraddresshere; Initial Catalog = databasenamehere; User ID = domain \ username; Password = password`
I doubt that this is related, but when I try to use Integrated Security it's getting my computer name instead of my own user name. I'm 99% sure this is because our anti-virus and monitoring software is spoofing an administrator user on top of my windows session to do its job. In any case, just entering the correct username and password should be working regardless, right?
我怀疑这是否相关,但是当我尝试使用集成安全性时,它会获取我的计算机名称而不是我自己的用户名。我99%肯定这是因为我们的防病毒和监控软件在我的Windows会话之上欺骗管理员用户来完成它的工作。无论如何,只要输入正确的用户名和密码就行了,对吧?
I took a look at some other questions involving the same error, but they were all unrelated. From what I can tell everyone who can get in through SSMS can access their SQL server through code, too. This is the most similar question I could find, although it didn't help much: Can't connect to SQL Server: "Login failed for user "."
我看了一些涉及相同错误的其他问题,但它们都是无关的。据我所知,每个可以通过SSMS进入的人都可以通过代码访问他们的SQL服务器。这是我能找到的最相似的问题,虽然它没有多大帮助:无法连接到SQL Server:“用户登录失败”。“
Also, before you ask, I HAVE ALREADY CHECKED FOR TYPOS. Actually, I've even tried to use other user's accounts to make sure there wasn't something funky going on with my permissions. If you need me to post any code let me know. Thanks for your help!
另外,在你问之前,我已经检查过TYPOS了。实际上,我甚至试图使用其他用户的帐户来确保我的权限不会出现任何问题。如果您需要我发布任何代码,请告诉我。谢谢你的帮助!
UPDATE:
I changed the account for the service and switched back to IntegratedSecurity = true. It seems like something has changed, because now I'm receiving a slightly different error:
我更改了服务帐户并切换回IntegratedSecurity = true。似乎有些事情发生了变化,因为现在我收到了一个稍微不同的错误:
Cannot open database "mydatabase" requested by the login. The login failed. >Login failed for user 'MYDOMAIN\myUserName'.
无法打开登录请求的数据库“mydatabase”。登录失败。 >用户'MYDOMAIN \ myUserName'登录失败。
3 个解决方案
#1
7
It's working! Finally!
它正在工作!最后!
First, using Kevin's suggestion (comment underneath the original question), I checked through the SQL Server Logs (in Management Studio, expand server, expand Management, expand SQL Server Logs). This is how I found more lengthy error reporting that helped me diagnose the issue.
首先,使用Kevin的建议(原始问题下面的评论),我检查了SQL Server日志(在Management Studio中,展开服务器,展开管理,展开SQL Server日志)。这就是我发现更冗长的错误报告,帮助我诊断问题的方法。
Turns out the server didn't allow anything other than Windows Authentication, so that's why my attempts at entering the UN/PW manually failed.
事实证明服务器不允许除Windows身份验证之外的任何其他内容,因此我尝试手动输入UN / PW失败了。
However, I still had the issue where the server was passing my machine name as the user instead of my username. This was fixed with Duncan's suggestion of changing the user that the service ran under to my name (right click in Services->Properties->Log On Tab).
但是,我仍然遇到服务器将我的机器名称作为用户而不是用户名传递的问题。这是通过Duncan建议将服务运行的用户更改为我的名字(右键单击Services-> Properties-> Log On选项卡)来解决的。
Thanks to everyone for your help, much appreciated.
感谢大家的帮助,非常感谢。
#2
2
You need to check the user account that the service is running under. By the sounds of things it is running as Local System which will try and pass the machine name through as the login.
您需要检查运行该服务的用户帐户。通过它作为本地系统运行的东西的声音,它将尝试通过机器名称作为登录。
Try changing this to a least privileged domain user (or for testing, you could use your own account) and then granting that user a login to the SQL Server. That should do the trick.
尝试将此更改为最低权限的域用户(或进行测试,您可以使用自己的帐户),然后授予该用户登录SQL Server的权限。这应该够了吧。
#3
0
What is the username you are passing to SQL Server to log in? According to the connection string, it looks like you are using the literal "domain\username" as the user. The password should be updated to be the correct password instead of the literal "password".
您要传递给SQL Server登录的用户名是什么?根据连接字符串,看起来您使用文字“域\用户名”作为用户。密码应更新为正确的密码,而不是文字“密码”。
I got this error quite a bit when I started out programming in C# writing a database backed application. I have a mixed mode environment for my application, and I pass the SQL Server username and password instead of what I use to log into my computer. This is a security policy I like to enforce so that the database controls who can log in and what permissions they get. Is that a fit for what you are trying to do? If so, I may be able to offer better assistance, as well as more specific code, too.
当我开始编写C#编写数据库支持的应用程序时,我得到了这个错误。我的应用程序有一个混合模式环境,我传递SQL Server用户名和密码,而不是我用来登录计算机的用户名和密码。这是我要强制执行的安全策略,以便数据库控制可以登录的用户以及他们获得的权限。这适合你想要做的事情吗?如果是这样,我也许可以提供更好的帮助,以及更具体的代码。
#1
7
It's working! Finally!
它正在工作!最后!
First, using Kevin's suggestion (comment underneath the original question), I checked through the SQL Server Logs (in Management Studio, expand server, expand Management, expand SQL Server Logs). This is how I found more lengthy error reporting that helped me diagnose the issue.
首先,使用Kevin的建议(原始问题下面的评论),我检查了SQL Server日志(在Management Studio中,展开服务器,展开管理,展开SQL Server日志)。这就是我发现更冗长的错误报告,帮助我诊断问题的方法。
Turns out the server didn't allow anything other than Windows Authentication, so that's why my attempts at entering the UN/PW manually failed.
事实证明服务器不允许除Windows身份验证之外的任何其他内容,因此我尝试手动输入UN / PW失败了。
However, I still had the issue where the server was passing my machine name as the user instead of my username. This was fixed with Duncan's suggestion of changing the user that the service ran under to my name (right click in Services->Properties->Log On Tab).
但是,我仍然遇到服务器将我的机器名称作为用户而不是用户名传递的问题。这是通过Duncan建议将服务运行的用户更改为我的名字(右键单击Services-> Properties-> Log On选项卡)来解决的。
Thanks to everyone for your help, much appreciated.
感谢大家的帮助,非常感谢。
#2
2
You need to check the user account that the service is running under. By the sounds of things it is running as Local System which will try and pass the machine name through as the login.
您需要检查运行该服务的用户帐户。通过它作为本地系统运行的东西的声音,它将尝试通过机器名称作为登录。
Try changing this to a least privileged domain user (or for testing, you could use your own account) and then granting that user a login to the SQL Server. That should do the trick.
尝试将此更改为最低权限的域用户(或进行测试,您可以使用自己的帐户),然后授予该用户登录SQL Server的权限。这应该够了吧。
#3
0
What is the username you are passing to SQL Server to log in? According to the connection string, it looks like you are using the literal "domain\username" as the user. The password should be updated to be the correct password instead of the literal "password".
您要传递给SQL Server登录的用户名是什么?根据连接字符串,看起来您使用文字“域\用户名”作为用户。密码应更新为正确的密码,而不是文字“密码”。
I got this error quite a bit when I started out programming in C# writing a database backed application. I have a mixed mode environment for my application, and I pass the SQL Server username and password instead of what I use to log into my computer. This is a security policy I like to enforce so that the database controls who can log in and what permissions they get. Is that a fit for what you are trying to do? If so, I may be able to offer better assistance, as well as more specific code, too.
当我开始编写C#编写数据库支持的应用程序时,我得到了这个错误。我的应用程序有一个混合模式环境,我传递SQL Server用户名和密码,而不是我用来登录计算机的用户名和密码。这是我要强制执行的安全策略,以便数据库控制可以登录的用户以及他们获得的权限。这适合你想要做的事情吗?如果是这样,我也许可以提供更好的帮助,以及更具体的代码。