Python pyodbc connect to Sql Server using SQL Server Authentication
The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.
my connection string :
connection = pyodbc.connect(
"Driver={"SQL Driver"};"
"Server= "ServerName";"
"Database="DatabaseName";"
"UID="UserName";"
"PWD="Password";"
"Trusted_Connection=yes"
)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);
How to connect to the database using sql server authentication ?
python
sql-server pyodbc sql-server-authentication asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
add a comment |
The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.
my connection string :
connection = pyodbc.connect(
"Driver={"SQL Driver"};"
"Server= "ServerName";"
"Database="DatabaseName";"
"UID="UserName";"
"PWD="Password";"
"Trusted_Connection=yes"
)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);
How to connect to the database using sql server authentication ?
python
sql-server pyodbc sql-server-authentication asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
AreUIDandPWDcredentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?
– Gord Thompson
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30
add a comment |
The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.
my connection string :
connection = pyodbc.connect(
"Driver={"SQL Driver"};"
"Server= "ServerName";"
"Database="DatabaseName";"
"UID="UserName";"
"PWD="Password";"
"Trusted_Connection=yes"
)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);
How to connect to the database using sql server authentication ?
python
sql-server pyodbc sql-server-authentication asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.
my connection string :
connection = pyodbc.connect(
"Driver={"SQL Driver"};"
"Server= "ServerName";"
"Database="DatabaseName";"
"UID="UserName";"
"PWD="Password";"
"Trusted_Connection=yes"
)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);
How to connect to the database using sql server authentication ?
python
sql-server pyodbc sql-server-authentication python
sql-server pyodbc sql-server-authentication asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
edited Nov 13 '18 at 3:18
Jack Lim
asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
asked Nov 13 '18 at 2:58
Jack LimJack Lim
478
478
AreUIDandPWDcredentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?
– Gord Thompson
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30
add a comment |
AreUIDandPWDcredentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?
– Gord Thompson
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30
Are
UID and PWD credentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?– Gord Thompson
Nov 13 '18 at 3:16
Are
UID and PWD credentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?– Gord Thompson
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30
add a comment |
1 Answer
1
active
oldest
votes
When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.
If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.
Trusted_Connection
Specifies whether a user connects through a user account by using
either Kerberos [RFC4120] or another platform-specific authentication
as specified by the fIntSecurity field (for details, see [MS-TDS]
section 2.2.6.4).
The valid values are "Yes", "1", or empty string, which are
equivalent, or "No". If the value "No" is not specified, the value
"Yes" is used.
If the value is "No", the UID and PWD keys have to be used to
establish a connection with the data source.
If the DSN key and the UID key are not included in the connection
string or if the value of the UID key is an empty string, the value of
the Trusted_Connection key has to be "Yes". If the Trusted_Connection
key is not specified in the connection string, the value has to be
obtained from the contents of the settings in the DSN key. If the
Trusted_Connection key is not specified in DSN or if the given DSN
does not exist, the default value is "No".
If the value of the Trusted_Connection key is "Yes", both the UID and
PWD keys are ignored. Otherwise, the UID key has to be specified.
In Microsoft implementations, this user account is a Windows user
account and NTLM authentication [MSDN-NTLM] is used when the value of
the Trusted_Connection key is "Yes".
source: https://msdn.microsoft.com/
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
add a comment |
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%2f53273146%2fpython-pyodbc-connect-to-sql-server-using-sql-server-authentication%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.
If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.
Trusted_Connection
Specifies whether a user connects through a user account by using
either Kerberos [RFC4120] or another platform-specific authentication
as specified by the fIntSecurity field (for details, see [MS-TDS]
section 2.2.6.4).
The valid values are "Yes", "1", or empty string, which are
equivalent, or "No". If the value "No" is not specified, the value
"Yes" is used.
If the value is "No", the UID and PWD keys have to be used to
establish a connection with the data source.
If the DSN key and the UID key are not included in the connection
string or if the value of the UID key is an empty string, the value of
the Trusted_Connection key has to be "Yes". If the Trusted_Connection
key is not specified in the connection string, the value has to be
obtained from the contents of the settings in the DSN key. If the
Trusted_Connection key is not specified in DSN or if the given DSN
does not exist, the default value is "No".
If the value of the Trusted_Connection key is "Yes", both the UID and
PWD keys are ignored. Otherwise, the UID key has to be specified.
In Microsoft implementations, this user account is a Windows user
account and NTLM authentication [MSDN-NTLM] is used when the value of
the Trusted_Connection key is "Yes".
source: https://msdn.microsoft.com/
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
add a comment |
When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.
If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.
Trusted_Connection
Specifies whether a user connects through a user account by using
either Kerberos [RFC4120] or another platform-specific authentication
as specified by the fIntSecurity field (for details, see [MS-TDS]
section 2.2.6.4).
The valid values are "Yes", "1", or empty string, which are
equivalent, or "No". If the value "No" is not specified, the value
"Yes" is used.
If the value is "No", the UID and PWD keys have to be used to
establish a connection with the data source.
If the DSN key and the UID key are not included in the connection
string or if the value of the UID key is an empty string, the value of
the Trusted_Connection key has to be "Yes". If the Trusted_Connection
key is not specified in the connection string, the value has to be
obtained from the contents of the settings in the DSN key. If the
Trusted_Connection key is not specified in DSN or if the given DSN
does not exist, the default value is "No".
If the value of the Trusted_Connection key is "Yes", both the UID and
PWD keys are ignored. Otherwise, the UID key has to be specified.
In Microsoft implementations, this user account is a Windows user
account and NTLM authentication [MSDN-NTLM] is used when the value of
the Trusted_Connection key is "Yes".
source: https://msdn.microsoft.com/
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
add a comment |
When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.
If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.
Trusted_Connection
Specifies whether a user connects through a user account by using
either Kerberos [RFC4120] or another platform-specific authentication
as specified by the fIntSecurity field (for details, see [MS-TDS]
section 2.2.6.4).
The valid values are "Yes", "1", or empty string, which are
equivalent, or "No". If the value "No" is not specified, the value
"Yes" is used.
If the value is "No", the UID and PWD keys have to be used to
establish a connection with the data source.
If the DSN key and the UID key are not included in the connection
string or if the value of the UID key is an empty string, the value of
the Trusted_Connection key has to be "Yes". If the Trusted_Connection
key is not specified in the connection string, the value has to be
obtained from the contents of the settings in the DSN key. If the
Trusted_Connection key is not specified in DSN or if the given DSN
does not exist, the default value is "No".
If the value of the Trusted_Connection key is "Yes", both the UID and
PWD keys are ignored. Otherwise, the UID key has to be specified.
In Microsoft implementations, this user account is a Windows user
account and NTLM authentication [MSDN-NTLM] is used when the value of
the Trusted_Connection key is "Yes".
source: https://msdn.microsoft.com/
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.
If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.
Trusted_Connection
Specifies whether a user connects through a user account by using
either Kerberos [RFC4120] or another platform-specific authentication
as specified by the fIntSecurity field (for details, see [MS-TDS]
section 2.2.6.4).
The valid values are "Yes", "1", or empty string, which are
equivalent, or "No". If the value "No" is not specified, the value
"Yes" is used.
If the value is "No", the UID and PWD keys have to be used to
establish a connection with the data source.
If the DSN key and the UID key are not included in the connection
string or if the value of the UID key is an empty string, the value of
the Trusted_Connection key has to be "Yes". If the Trusted_Connection
key is not specified in the connection string, the value has to be
obtained from the contents of the settings in the DSN key. If the
Trusted_Connection key is not specified in DSN or if the given DSN
does not exist, the default value is "No".
If the value of the Trusted_Connection key is "Yes", both the UID and
PWD keys are ignored. Otherwise, the UID key has to be specified.
In Microsoft implementations, this user account is a Windows user
account and NTLM authentication [MSDN-NTLM] is used when the value of
the Trusted_Connection key is "Yes".
source: https://msdn.microsoft.com/
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
edited Nov 13 '18 at 4:15
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
answered Nov 13 '18 at 4:00
Paulo ScardinePaulo Scardine
39.1k887117
39.1k887117
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53273146%2fpython-pyodbc-connect-to-sql-server-using-sql-server-authentication%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
Are
UIDandPWDcredentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code?– Gord Thompson
Nov 13 '18 at 3:16
Are you able to login using the same credentials with other database client?
– Paulo Scardine
Nov 13 '18 at 3:16
@PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS.
– Jack Lim
Nov 13 '18 at 3:20
@GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password.
– Jack Lim
Nov 13 '18 at 3:30