Sql-server – Access for multiple domain users to SQL Server using Windows Authentication

sql serversql-server-2012

We are delivering SQL Server 2012 SP1 Express edition as part of database with our application. We setup SQL Server express edition as silent installer that user can choose to install or not.

However, I am seeing problem with windows authentication when Domain\User1 setup SQL Server and Domain\User2 on same machine tries to connect to the same instance created by User1. Though, User2 can log in to the instance created by User1, User2 can't create database or access database created by User1. Following is the error message shown when User1 tries to access database.

I know that we can add domain users when installing SQL Server manually. However, because we should setup SQL Server as silent installer with command-line parameter, We can't add other domain users. Is there anyway that we can allow multiple domain users to connect to database while still using Windows Authentication?

Best Answer

For troubleshooting purposes you could fine-tune your deployment after installing SQL Express by attaching this one-liner. Feel free to get more granular with security as needed--perhaps a more exclusive user group besides the local USERS group (and DB_OWNER--hopefully is overkill and can be something much less):

sqlcmd -E -S %COMPUTERNAME%\SQLEXPRESS -Q "CREATE LOGIN [BUILTIN\USERS] FROM WINDOWS; USE <your  database>;CREATE USER [BUILTIN\USERS];EXEC SP_ADDROLEMEMBER DB_OWNER,[BUILTIN\USERS]"

p.s. be sure and change to the actual database name of the local SQL Express database. And if SQLEXPRESS is not the actual instance name--change that too. There are alternate syntax variants available as you progress up the SQL 20XX versions.

Related Solutions

Sql-server – SQL Server 2008: The login is from an untrusted domain and cannot be used with Windows authentication

Since you are running in a workgroup the only possible authentication is one using 'mirrored NT accounts', which means that the process trying to authenticate must be running under an identity that is 'mirrored' on the host that is authenticating the process. 'Mirrored' accounts are distinct local accounts with identical name and password on both hosts doing the authentication.

So it must follow that you're running SSMS from an account that it is mirrored on the SQL Server host (eg. <computername>\Administrator, which is mirrored by the <sqlhost>\Administrator and has the same password). Note that UAC has nothing to do here (ie running as administrator will not help).

Your application is probably running under a different local account (default IIS appool perhaps?) and the account under which the application runs is not mirrored on the SQL Server host.

Your best solution is to ditch the makeshift network and deploy a proper AD (domain). As an interim, make sure you run your application under an account that is mirrored on thew SQL Server host machine.

Sql-server – Connect to SQL Server using windows authentication from another PC without Active Directory

If none of the machines involved have Active Directory authentication enabled via Kerberos, which precludes using an online-based Microsoft Account (Windows 8 and higher), you should be able to connect to SQL Server using Windows Authentication assuming you follow these points:

Ensure SQL Server is configured to use TCP connections.
Ensure the Windows firewall is either disabled or has SQL Server connections allowed.
The machine where SQL Server is installed needs Windows user accounts for all the people who will be connecting via the network. For instance, if the SQL Server computer is named MyPC, and you have User1, User2, and User3 who will be using the SQL Server, MyPC must have accounts setup for User1, User2, and User3 with the same password those user accounts use on their own PC.

You create logins in SQL Server for those user accounts, i.e.:

CREATE LOGIN [MyPC\User1] FROM WINDOWS;
CREATE LOGIN [MyPC\User2] FROM WINDOWS;
CREATE LOGIN [MyPC\User3] FROM WINDOWS;

The client machines may need to connect to the server using the server's IP address instead of it's name. If the server's IP address is 192.168.0.100, and it is listening on port 33333 then they would use this as the server name to connect to:
```
tcp:192.168.0.100,33333
```

If you follow all the above advice, then receive the following error when connecting, then the client PC is using Kerberos authentication, and nothing will help you connect.

The target principal name is incorrect. Cannot generate SSPI context.

Best Answer

Related Solutions

Sql-server – SQL Server 2008: The login is from an untrusted domain and cannot be used with Windows authentication

Sql-server – Connect to SQL Server using windows authentication from another PC without Active Directory

Related Question