How do I add NETWORK SERVICE login via SQL Authentication?

Question

I am trying to add the NETWORK SERVICE login cause I still can’t connect to the AdventureWorks3 database. But I want to add this NETWORK SERVICE login via SQL Server Authentication and not Windows.

When I select Windows authentication, it lets me create this login. But when I select SQL Server Auth, it gives me the following error: Create failed for Login ‘NT AUTHORITY\NETWORK SERVICE’…’NT AUTHORITY\NETWORK SERVICE’ is not a valid name because it contains invalid characters. (MS SQL Server, Error: 15006)

Answer 1

SQL authentication and Windows authentication are distinct. The two are not interchangeable by specifying similar usernames.

SQL auth enables the storage of password information within the database and are portable, while Windows auth tells SQL to rely on Windows for authenticating users. Therefore, you don't specify passwords along with Windows auth users in the database.

If you need to use SQL authentication, just specify any username and password and include that information in your connection string. There's no reason why the name must match existing Windows users.

Answer 2

NT AUTHORITY\NETWORK SERVICE is the local server account name. When the server is part of a domain, "network service" becomes DOMAIN\ServerName$ and this can be used for a Trusted/integrated/Windows login

CREATE LOGIN [BigCompany\JustOneServer$] FROM WINDOWS

If it's not part of a domain, use a SQL Server login.