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 ?

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

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

@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 |

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 ?

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

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

@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 |

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 ?

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

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

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

edited Nov 13 '18 at 3:18

asked Nov 13 '18 at 2:58

Jack Lim

478

asked Nov 13 '18 at 2:58

Jack Lim

478

asked Nov 13 '18 at 2:58

Jack Lim

478

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

@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 |

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

@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 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 |

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/

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo 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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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/

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo 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/

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo 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/

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo 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/

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo Scardine

39.1k887117

edited Nov 13 '18 at 4:15

answered Nov 13 '18 at 4:00

Paulo Scardine

39.1k887117

answered Nov 13 '18 at 4:00

Paulo Scardine

39.1k887117

answered Nov 13 '18 at 4:00

Paulo Scardine

39.1k887117

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Wsrtjtyk