在亚马逊EC2上使用集成安全性连接到SQL Express失败

Question

我正在Amazon EC2上运行Windows Server 2019的t2-micro实例。我已经安装了用于Web开发的SQL Server和IIS。我已经使用压缩包部署了一个asmx web服务，并使用SSMS手动创建了数据库。使用以下连接字符串将导致错误

 static string Source = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=coredatabase;Integrated Security=SSPI;";

SSMS可以很好地与此连接配合使用

​

我得到以下错误

System.Data.SqlClient.SqlException: Cannot open database "coredatabase" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\DefaultAppPool'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at SIT_Service.CoreService.checkifuserexistsinternal(String email, String mainauthtoken) in C:\Users\User\Desktop\Web Service\Web Service (1)\Web Service\WebApplication2\CoreService.asmx.cs:line 429
   at SIT_Service.CoreService.register(String email, String password, String mainauthtoken) in C:\Users\User\Desktop\Web Service\Web Service (1)\Web Service\WebApplication2\CoreService.asmx.cs:line 498

Answer 1

错误显示："Login failed for user IIS APPPOOL\DefaultAppPool"，这意味着用户IIS APPPOOL\DefaultAppPool的登录失败。

可能是因为该Windows用户没有SQL Server登录名。在SSMS中，在查询窗口中连接到数据库并运行

use coredatabase
create login [IIS APPPOOL\DefaultAppPool] from windows;
create user [IIS APPPOOL\DefaultAppPool] for login [IIS APPPOOL\DefaultAppPool];
grant select, insert, update, delete to [IIS APPPOOL\DefaultAppPool];  --or whatever