Connecting to SQL via IIS using integrated authentication

I have a web server on my domain that i want to use to connect to an SQL database.

Ideally i want my users to log on to the web server using integrated authentication, then the web server pass the same details on to the SQL server so i can use the built in audit tools.

I have enabled  Windows authentication and ASP.net Impersonate in IIS

my web.config file has the below

  <system.web>
    <compilation targetFramework="4.5.1" />
    <httpRuntime targetFramework="4.5.1" maxRequestLength="50000" /> <!-- 50mb -->
    <authentication mode="Windows"/>
  </system.web>

and

<connectionStrings>
 <add name="sitename" connectionString="Data Source=servername; Initial Catalog=databasename;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
  </connectionStrings>

We are running on port 8081 as this is a back end API, i have added an SPN of setspn -s http/hostname:8081 hostname  (unsure if i had to do this i thought i would try it but didnt make a difference)

we are using web API to return the login name (user.identity.name) which returns fine on a page that just returns the information but to connect to the SQL server we are using entity framework which uses the above connection string.

We have a test page using the indentity name and it returns all the details fine on local host and remote host however for the entity framework we get this message {"Message":"Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'."}

Is there an easy way to resolve this or an easier way to create a trusted SQL connection on an intranet enviornment.

Thanks in advance for any help provided.
LVL 6
CaptainGibletsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You need to create a NT domain account in SQL and grant it permissions to the databases that your APP needs to use then use that account in all connection strings not "Anonymous".
More details at:

https://support.microsoft.com/en-us/kb/247931
https://msdn.microsoft.com/en-us/library/bsz5788z.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eugene ZCommented:
you need to check
and adjust as needed your IIS security feature called the application pool identity
more:

Application Pool Identity
http://www.iis.net/learn/manage/configuring-security/application-pool-identities
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
{"Message":"Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'."}
Are you connecting to the test webpage with a valid domain user?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.