Add a computer account as a sql server login
Question
Saturday, December 11, 2010 6:02 PM | 1 vote
Hi All,
I have very strange request from my application team and they want to add the server name as a login in sql server.
Can anybody help with the way to do it.....
I am able to add the computer name as a member of the local administrators and not able to do the same for the sql server logins....
Regards
Nimesh
All replies (11)
Saturday, December 11, 2010 7:08 PM âś…Answered | 8 votes
You can add an AD account using CREATE LOGIN:
CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS;
But I agree this is a strange request. Typically, one would use a service account rather than the machine account for database access.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Saturday, December 11, 2010 7:47 PM | 1 vote
Dan is correct. I have seen few products which this kind of requirement.
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq
Friday, January 21, 2011 10:37 AM
CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS; does not work: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near \ Why do you think the request is strange? Doesn't SCCM do exactly the same?
Friday, January 21, 2011 1:14 PM
CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS; does not work: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near \
This should work. Perhaps the actual account name you used was not specified correctly; I get the same error if I try "[MyDomain]\MyComputer$]" instead of "[MyDomain\MyComputer$]".
Why do you think the request is strange? Doesn't SCCM do exactly the same?
I don't know much about SCCM but system software is different than application software. In Nimesh's case, it is the application team that requested the login, presumably for use by application code. If Nimesh adds a computer account as a login, then *any* process that runs under the computer account on that machine will have the same login and rights. Routine application database access is typically done via a managed service account or specialized domain account.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Tuesday, October 15, 2013 2:52 PM
how would i create the service account to do the same as a machine account would?
Monday, June 30, 2014 9:40 PM | 1 vote
Thanks for solution. SQL Server Multi-Server administration requires the use of the machine account of a target when the target agent is using a virtual account. Normally, the Multi-Server GUI would use the machine account; however, it appears that the local virtual account on the master will be used if it has the same name as the target virtual account. The result is an enlist error
The enlist operation failed (reason: SQLServerAgent Error: Unable to conenct to MSX
<server_name>) (Microsoft SQL Server Error 22026)
I tried to add the computer account with the SSMS GUI, but that also failed. The T-SQL was the way to go. After adding access for the machine account, I was able to enlist the target without error.
Here is the script I used on the master job server for a target "SQLBOX1".
USE [master];
CREATE LOGIN [ADMIN\SQLBOX1$] FROM WINDOWS;
GO
USE [msdb];
CREATE USER [ADMIN\SQLBOX1$] FOR LOGIN [ADMIN\SQLBOX1$]
ALTER ROLE [TargetServersRole] ADD MEMBER [ADMIN\SQLBOX1$]
GO
Randy in Marin
Wednesday, January 11, 2017 6:11 PM
Hi Dan,
Is there a way i can add a machine on a WORKGROUP to the SQL Security instead of it being a domain.
something like WORKGROUP\MACHINENAME$
Please advice.
Srini
Wednesday, January 11, 2017 6:36 PM
Hello Srini,
No, in a Workgroup environment this is not possible; use SQL Logins for your services/applications instead.
And please don't post on such old, closed thread, start a new one.
Olaf Helper
Wednesday, February 8, 2017 7:25 AM
I also faced the same error.
I solved the issue by removing $ and typing it using keyboard.
because I initially copied "[MyDomain\MyComputer$]" from error window and paste it directly.
it seems when you paste it , it is not using correct Unicode.
Turabek Molodjanov
Monday, April 24, 2017 7:00 AM
request as request, nothing strange..
suppose you have web site with connection string "data source=server; initial catalog=database; integrated security =SSPI" and web site is hosted on IIS on other server than database with sql, then you need to add computer account to sql server with windows (NOT mixed) authentification to get access to the data or look for another solution to access data :)
Friday, June 15, 2018 3:58 PM
This is not necessarily such a strange request; if you have an IIS application that needs to access the SQL server, Microsoft's recommended practice is to set the Identity of its Application Pool to "NetworkService." The doc for this says:
"Using the Network Service account in a domain environment has a great benefit. Worker process running as Network Service access the network as the machine account. Machine accounts are generated with a machine is joined to a domain. The nice thing about this is that network resources like file shares or SQL Server databases can be ACLed to allow this machine account access."
That is, by setting up a login in the SQL server for the IIS host, it allows that host's applications to then use integrated security to access the database, without requiring specification of usernames and passwords either in the code or web.config files to gain that access.