locked
Access sql server using SQL server authentication from the outside of domain? RRS feed

  • Question

  • I wrote a program by python that ran on a machine that is outside of the domain and needed to connect to a sql server 2012 that was inside of the domain. I used SQL server authentication. When I connected to sql server, I got the error message, Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    I didn't use windows authentication. Why did it say I used with windows authentication? Is it possible to access sql server with SQL server authentication from outside of domain?

    I have done the followings.

    1. Open the port 1433 on firewall on the machine of sql server.

    2. Enable TCP/IP in Sql server configuration manager and don't use dynamic port.

    3. Create a login account for SQL server authentication on sql server. It can work well between machines that are located in domain.

    4. Set mix authentication mode on sql server.

    5. Create a dsn with SQL server authentication.

    6. Connection string in python: "DSN=myodbc;trusted_connection=true"

    Except for above, anything else I shoul set up or configure?

    Thanks.


    • Edited by Joe CL Hsu Thursday, July 4, 2013 11:24 PM
    Thursday, July 4, 2013 11:32 AM

Answers

  • Hi,

    In your connection string change the trusted_connection to false,

    • True means use Windows integrated security to log in to SQL Server, even if the connection string includes User ID and Password settings.

    • False means use SQL Server security to log in by using the User ID and Password values in the connection string, and raise an exception if they are not present.

    http://msdn.microsoft.com/en-us/library/jj653752.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Thursday, July 4, 2013 11:55 AM
    Answerer
  • >>>I didn't use windows authentication. Why did it say I used with windows authentication? Is

    But this is written in your connection string

    6. Connection string in python: "DSN=myodbc;trusted_connection=true"


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, August 2, 2013 3:37 AM
    Answerer

All replies

  • Hi,

    In your connection string change the trusted_connection to false,

    • True means use Windows integrated security to log in to SQL Server, even if the connection string includes User ID and Password settings.

    • False means use SQL Server security to log in by using the User ID and Password values in the connection string, and raise an exception if they are not present.

    http://msdn.microsoft.com/en-us/library/jj653752.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Thursday, July 4, 2013 11:55 AM
    Answerer
  • Hi,

    I changed trusted_connection to false. It still got the same error.

    Any suggestions?

    Thanks.

    Friday, July 5, 2013 10:10 AM
  • I seemed to find the solution about this issue.

    The login account for SQL server should have sysadmin role.

    Thanks.

    Friday, August 2, 2013 12:43 AM
  • >>>I didn't use windows authentication. Why did it say I used with windows authentication? Is

    But this is written in your connection string

    6. Connection string in python: "DSN=myodbc;trusted_connection=true"


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, August 2, 2013 3:37 AM
    Answerer