Cannot connect to SQL Server except with domain admin account

Question

I am running into an issue where my .net application (C#) will not connect to SQL Server when ran by a domain user.

Exception is:

The underlying provider failed on open. >> Network related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. >> The network path was not found.

If I run this app in Visual Studio, no issues connecting. Also I can run the exe as a domain admin and no issues connecting. Other domain admins alongside me have no issues running the app via the exe either.

Domain users get the exception outlined above. I've been able to recreate with a test domain user, bumped it to admin and no longer hit the outlined exception.

This has my team stumped.

Any ideas?

• TCP/IP enabled on server
• Remote connections enabled
• Looked into user permissions
• Not using integrated security in connection string
• I can ping the server this SQL Server is running on

Answer 1

The Domain admins are by default part of the administrator group of the server in the domain. As they are part of the local administrators in the SQL Server machine, they are having sysadmin rights to the SQL Server instance, by default. So, the domain administrators are able to connect to the SQL Server instance without issues.

• Default groups
• Fixed Server Roles

But, for the normal domain user, as he is not having permission to connect to SQL Server, he/she is getting error in connecting.

What we have to do is, either of the following:

• Create an AD security group and add the domain user part of the security group. Give this security group permission in Sql server.
• Add each domain user individually to the SQL Server instance. It is a laborious process.