0

I could not access SQL Server 2012 remotely using windows user account, even though its possible to access SQL Server after login to Windows OS with the same credentials.

Is there any way to achieve that?

Due to Security (OS Hardening) reason's SQL Server users are not supported

Trying to Access SQL 2012 remotely from CentOS

telnet host 1433 #Works fine

TCP enabled in SQL Configuration manager.

pymssql version - 2.1.0

pymssql.connect('192.168.1.7', 'mydomain\\dba', 'password', 'testdb')

I'm getting below error

Traceback (most recent call last): File "<stdin>", line 1, in <module> File "pymssql.pyx", line 599, in pymssql.connect (pymssql.c:9315) pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')

FreeTDS configuration

tsql -C Compile-time settings (established with the "configure" script) Version: freetds v0.91 freetds.conf directory: /etc MS db-lib source compatibility: yes Sybase binary compatibility: yes Thread safety: yes iconv library: yes TDS version: 4.2 iODBC: no unixodbc: yes SSPI "trusted" logins: no Kerberos: yes

user2264738
  • 302
  • 4
  • 18

1 Answers1

1

Yes, this can be done during the connection:

pymssql.connect(
    server='192.168.1.7:1433',
    user='mydomain\\dba',
    password='password',
    database='testdb',
    tds_version='7.2'
)

Please note you'll have to be sure you can connect to the server SQL Server is running on from the machine you're running pymssql on. If things are locked down, you made need firewall modifications to be made. You can test to see if you can connect to your SQL Server server with telnet: telnet your_host 1433 to see if it connects, or just hangs.

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • **pymssql.connect('192.168.1.7', 'domain\\dba', 'password', 'testdb')** I'm getting below error `Traceback (most recent call last): File "", line 1, in File "pymssql.pyx", line 599, in pymssql.connect (pymssql.c:9315) pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')` telnet worked fine... – user2264738 Sep 14 '17 at 13:13
  • Can you connect and get the `1>` prompt if you try: `TDSVER=7.2 tsql -H your_host -p 1433 -U YOUR_DOMAIN\\your_username -D your_database_name`? – FlipperPA Sep 14 '17 at 14:58
  • I've also amended my answer, as I'm guessing your TDS version isn't being set correctly, and defaulting to version `4.2`. – FlipperPA Sep 14 '17 at 15:05
  • I tried with TDSVER 7.2 AND 8 getting the same error.... https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a20bd47-a714-451a-a0f5-10d3262a8291/is-it-possible-using-pymssql-to-access-sql-server-2012-remotely-with-domain-windows-user-account?forum=sqlgetstarted – user2264738 Sep 15 '17 at 12:43