.Net Core 2.1 Web Api connection string using supplied User Id and Password for SQL Connection
I've been trying to get my application to use the supplied user id and password for connecting to my SQL Server but can't get anything to work.
Here is the setup, I have a laptop that I am developing on and a desktop that I am hosting the webapi on as well as the sql server. When I run locally on the laptop connecting to the desktop sql server everything works (using User Id and Password). However when I publish to the desktop and try to connect I get errors connecting to the sql server.
I've tried all settings for Trusted_Connection and Integrated Security as well as not supplying either.
Anything with Trusted_Connection set to false gives me these errors in the Windows Event Logs
- "Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied."
- "Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user."
Anything with Trusted_Connection set to true tries to connect to the SQL Server using the IIS APPPOOL… identity.
"DefaultConnection": "Server=xxx\SQLEXPRESS;Database=databaseName;User Id=userId;Password=password;Trusted_Connection=False;Integrated Security=False;"
c# database-connection asp.net-core-webapi sql-server-express
edited Nov 19 '18 at 6:24
JohnB
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
add a comment |
I've been trying to get my application to use the supplied user id and password for connecting to my SQL Server but can't get anything to work.
Here is the setup, I have a laptop that I am developing on and a desktop that I am hosting the webapi on as well as the sql server. When I run locally on the laptop connecting to the desktop sql server everything works (using User Id and Password). However when I publish to the desktop and try to connect I get errors connecting to the sql server.
I've tried all settings for Trusted_Connection and Integrated Security as well as not supplying either.
Anything with Trusted_Connection set to false gives me these errors in the Windows Event Logs
- "Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied."
- "Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user."
Anything with Trusted_Connection set to true tries to connect to the SQL Server using the IIS APPPOOL… identity.
"DefaultConnection": "Server=xxx\SQLEXPRESS;Database=databaseName;User Id=userId;Password=password;Trusted_Connection=False;Integrated Security=False;"
c# database-connection asp.net-core-webapi sql-server-express
edited Nov 19 '18 at 6:24
JohnB
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use.SQLEXPRESS;...to designate the local machine as the server.
– Crowcoder
Nov 18 '18 at 23:29
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58
add a comment |
I've been trying to get my application to use the supplied user id and password for connecting to my SQL Server but can't get anything to work.
Here is the setup, I have a laptop that I am developing on and a desktop that I am hosting the webapi on as well as the sql server. When I run locally on the laptop connecting to the desktop sql server everything works (using User Id and Password). However when I publish to the desktop and try to connect I get errors connecting to the sql server.
I've tried all settings for Trusted_Connection and Integrated Security as well as not supplying either.
Anything with Trusted_Connection set to false gives me these errors in the Windows Event Logs
- "Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied."
- "Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user."
Anything with Trusted_Connection set to true tries to connect to the SQL Server using the IIS APPPOOL… identity.
"DefaultConnection": "Server=xxx\SQLEXPRESS;Database=databaseName;User Id=userId;Password=password;Trusted_Connection=False;Integrated Security=False;"
c# database-connection asp.net-core-webapi sql-server-express
edited Nov 19 '18 at 6:24
JohnB
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
I've been trying to get my application to use the supplied user id and password for connecting to my SQL Server but can't get anything to work.
Here is the setup, I have a laptop that I am developing on and a desktop that I am hosting the webapi on as well as the sql server. When I run locally on the laptop connecting to the desktop sql server everything works (using User Id and Password). However when I publish to the desktop and try to connect I get errors connecting to the sql server.
I've tried all settings for Trusted_Connection and Integrated Security as well as not supplying either.
Anything with Trusted_Connection set to false gives me these errors in the Windows Event Logs
- "Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied."
- "Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user."
Anything with Trusted_Connection set to true tries to connect to the SQL Server using the IIS APPPOOL… identity.
"DefaultConnection": "Server=xxx\SQLEXPRESS;Database=databaseName;User Id=userId;Password=password;Trusted_Connection=False;Integrated Security=False;"
c# database-connection asp.net-core-webapi sql-server-express
c# database-connection asp.net-core-webapi sql-server-express
edited Nov 19 '18 at 6:24
JohnB
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
edited Nov 19 '18 at 6:24
JohnB
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
edited Nov 19 '18 at 6:24
JohnB
1,78311117
edited Nov 19 '18 at 6:24
JohnB
1,78311117
edited Nov 19 '18 at 6:24
JohnB
1,78311117
1,78311117
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
asked Nov 18 '18 at 22:58
Tony EckstrandTony Eckstrand
111
111
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use.SQLEXPRESS;...to designate the local machine as the server.
– Crowcoder
Nov 18 '18 at 23:29
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58
add a comment |
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use.SQLEXPRESS;...to designate the local machine as the server.
– Crowcoder
Nov 18 '18 at 23:29
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use
.SQLEXPRESS;... to designate the local machine as the server.– Crowcoder
Nov 18 '18 at 23:29
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use
.SQLEXPRESS;... to designate the local machine as the server.– Crowcoder
Nov 18 '18 at 23:29
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53366270%2fnet-core-2-1-web-api-connection-string-using-supplied-user-id-and-password-for%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53366270%2fnet-core-2-1-web-api-connection-string-using-supplied-user-id-and-password-for%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It's nothing to do with the user and password. If the app is on the same machine as the server you should be able to use
.SQLEXPRESS;...to designate the local machine as the server.– Crowcoder
Nov 18 '18 at 23:29
Side note: if you can use Integrated Security (Trusted Connection) it would be more secure, just run the app pool under a user account with the least privilege necessary to perform the database operations you require.
– Crowcoder
Nov 18 '18 at 23:35
So I added trusted connection, gave my IIS APPPOOLTestAppPool a login for the database and read/write permissions on my database (same permissions that my passed used credentials had and could use the site locally). I tried changing connection to .SQLEXPRESS. I can't get it to login successfully, now I get Login failed for user IIS APPPOOLTestAppPool. So it is trying that user but it seems like the SQL server isn't allowing that user to login when though they have a server login security and a database user security.
– Tony Eckstrand
Nov 22 '18 at 4:09
"... a login for database..." doesn't make sense because logins are for the server. Users are given access at the database level. And the AppPool identity cannot be given a password so you need a regular Windows account under which to run the app pool.
– Crowcoder
Nov 22 '18 at 10:58