1

I'm creating an ASP.NET application for my company.

In Visual Studio, I used EntityFramework to connect to my SQL Server database with an edmx file. The application is using Windows authentication to connect to both the website and SQL Server.

When I run the application locally, all is working great. The Windows authentication works so my app know who I am, then it connects to SQL Server correctly and shows up data.

But when I try to deploy it on my IIS Server, I get the following error :

Login failed for user 'DOMAIN\SERVERNAME$'.

The Windows authentication works great to identify who I am in the app, but the integrated security used to connect to SQL Server is using the server identity instead of the identity of the application's user.

What you may want to know :

  • All users have an SQL Server account to connect through integrated security
  • The server is on the same Domain than our computers
  • The SQL Server is on another server than the machine where IIS is running, but the remote connection is activated and working
  • I'm using Google Chrome to test my app locally and remotely

There is the connection string I am using :

<connectionStrings>
<add name="DatabaseEntities" connectionString="metadata=res://*/Models.DatabaseModel.csdl|res://*/Models.DatabaseModel.ssdl|res://*/Models.DatabaseModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SQLSERVERNAME\INSTANCE;initial catalog=Database;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Is it possible to use the same Windows authentication for both ASP.NET application and SQL Server ?

TylerH
  • 20,799
  • 66
  • 75
  • 101
M. Ozn
  • 1,018
  • 1
  • 19
  • 42

2 Answers2

0

You should add your domain user to SQL-server logins - look here

Or remove integrated security option from connection string and add user id and password of sql server user:

<add name="DbConnectionString" connectionString="Data Source=SERVER;Initial Catalog=db;User Id=sqlusername;Password=sqluserpass;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient"/>

note that exampled connection string does not contains metadata for model-first approach

Artem Kurianov
  • 64
  • 8
  • As I said all my users are already added in SQL Server. This is already working in an ancient application, users can connect to SQL Server through Windows authentication. The point here is I want to use integrated security so I can't remove it – M. Ozn Apr 20 '18 at 12:29
0

I was looking on how to set this up for SQL FILESTREAM in my API.

Update the Host File with the IPV4 for the FQDN or SERVERNAME of the SQL Server.

In Credential Manager add 2 Credentials.
One for File Share the other Port 1433.
The File Transfer does not require the Domain, but the Port 1433 version does.
Window_Credential_Manager

After this was able to use Trusted Connection or Integrated Security=SSPI to access the FILESTREAM.

juanvan
  • 671
  • 7
  • 19