domain login fails in isql but works with tsql

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

domain login fails in isql but works with tsql

Eric Robinson (erirobin)

Hi, when trying to connect from Linux to MSSQL Server 2005 via isql my connection fails using a NT Domain account. 

 

Should isql work with a domain account? If so what is the proper setup?

 

I have verified TDFREE drivers are working properly by connecting via tsql using the domain account.

 

Working:  tsql -S <tsfree.conf entry>  -p 1433 -U <domain><a href="file:///\\%3cuserid">\\<userid>

 

I believe most of the setup is correct as I can see the login attempt on the proper server:database but it does not appear to parse the domain\userid properly.

 

Not working : isql -v <odbc.ini entry>   <domain><a href="file:///\\%3cuserid">\\<userid>

 

Linux side error msg:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

[37000][unixODBC][FreeTDS][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

 

Sqlserver 2005 side error message:

 

04/14/2010 22:18:38,Logon,Unknown,Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.81.247.6]

04/14/2010 22:18:38,Logon,Unknown,Error: 18452<c/> Severity: 14<c/> State: 1.

04/14/2010 22:18:38,Logon,Unknown,SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.81.247.6]

04/14/2010 22:18:38,Logon,Unknown,Error: 17806<c/> Severity: 20<c/> State: 2.

 

Thanks, Eric


_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nick Gorham-2
Eric Robinson (erirobin) wrote:
>
> Hi, when trying to connect from Linux to MSSQL Server 2005 via isql my
> connection fails using a NT Domain account.
>
>  
>
> Should isql work with a domain account? If so what is the proper setup?
>
Hi,

You are best asking that question of the FreeTDS folk.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Eric Robinson (erirobin)
Nick,

I don't feel there is any issue with FreeTDS and the connection works 3
different ways(albeit all provided by FreeTDS):

1) Bypassing freetds.conf via tsql and connecting directly to the db
host like this:
        tsql -H <physical db host> -p 1433 -U <domain/userid>
2) Using tsql via the settings in freetds.conf
        tsql -S <freetds.conf entry> -p 1433 -U <domain/userid>
3) Using fisql via the settings in freetds.conf
        fisql -S <freetds.conf entry> -U <domain/userid>

All of which are provided by freetds.

I only get errors when accessing via UNIXodbc manager.


-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Nick
Gorham
Sent: Thursday, April 15, 2010 3:19 AM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] domain login fails in isql but works
with tsql

Eric Robinson (erirobin) wrote:
>
> Hi, when trying to connect from Linux to MSSQL Server 2005 via isql my

> connection fails using a NT Domain account.
>
>  
>
> Should isql work with a domain account? If so what is the proper
setup?
>
Hi,

You are best asking that question of the FreeTDS folk.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nick Gorham-2
Eric Robinson (erirobin) wrote:
> Nick,
>
> I don't feel there is any issue with FreeTDS and the connection works 3
> different ways(albeit all provided by FreeTDS):
>  
Ok, with respect, you are free to have that feeling.

> 1) Bypassing freetds.conf via tsql and connecting directly to the db
> host like this:
> tsql -H <physical db host> -p 1433 -U <domain/userid>
> 2) Using tsql via the settings in freetds.conf
> tsql -S <freetds.conf entry> -p 1433 -U <domain/userid>
> 3) Using fisql via the settings in freetds.conf
> fisql -S <freetds.conf entry> -U <domain/userid>
>
> All of which are provided by freetds.
>
> I only get errors when accessing via UNIXodbc manager.
>  

No, you only have that error when accessing via ODBC from what I can see.

The error you reported:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Shows clearly that the driver manager has located and loaded the FreeTDS
driver, and has passed the connection request to it, the error is
returned to the driver manager from the driver.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Eric Robinson (erirobin)
Ok, thanks.. I will follow up with them also.

I appreciate the feedback.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Nick
Gorham
Sent: Thursday, April 15, 2010 10:13 AM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] domain login fails in isql but works
with tsql

Eric Robinson (erirobin) wrote:
> Nick,
>
> I don't feel there is any issue with FreeTDS and the connection works
3
> different ways(albeit all provided by FreeTDS):
>  
Ok, with respect, you are free to have that feeling.

> 1) Bypassing freetds.conf via tsql and connecting directly to the db
> host like this:
> tsql -H <physical db host> -p 1433 -U <domain/userid>
> 2) Using tsql via the settings in freetds.conf
> tsql -S <freetds.conf entry> -p 1433 -U <domain/userid>
> 3) Using fisql via the settings in freetds.conf
> fisql -S <freetds.conf entry> -U <domain/userid>
>
> All of which are provided by freetds.
>
> I only get errors when accessing via UNIXodbc manager.
>  

No, you only have that error when accessing via ODBC from what I can
see.

The error you reported:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Shows clearly that the driver manager has located and loaded the FreeTDS

driver, and has passed the connection request to it, the error is
returned to the driver manager from the driver.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Eric Robinson (erirobin)
In reply to this post by Nick Gorham-2
So UNIXodbc and FreeTDS both are working fine now :)

My end goal was to use Oracle DG4ODBC -> UNIXodbc -> FreeTDS to access
MSSQL from Oracle using a domain\userid and that is working fine.

Apparently there is a bug in the isql on the box I am on and I wrongly
assumed it was provided by UNIXodbc (my bad). It is not associated with
any RPM so I have no clue what installed it, probably provided by
RedHat.

Thanks again for your responses,

Eric

-----Original Message-----
From: Eric Robinson (erirobin)
Sent: Thursday, April 15, 2010 10:16 AM
To: 'Support for the unixODBC project'
Subject: RE: [unixODBC-support] domain login fails in isql but works
with tsql

Ok, thanks.. I will follow up with them also.

I appreciate the feedback.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Nick
Gorham
Sent: Thursday, April 15, 2010 10:13 AM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] domain login fails in isql but works
with tsql

Eric Robinson (erirobin) wrote:
> Nick,
>
> I don't feel there is any issue with FreeTDS and the connection works
3
> different ways(albeit all provided by FreeTDS):
>  
Ok, with respect, you are free to have that feeling.

> 1) Bypassing freetds.conf via tsql and connecting directly to the db
> host like this:
> tsql -H <physical db host> -p 1433 -U <domain/userid>
> 2) Using tsql via the settings in freetds.conf
> tsql -S <freetds.conf entry> -p 1433 -U <domain/userid>
> 3) Using fisql via the settings in freetds.conf
> fisql -S <freetds.conf entry> -U <domain/userid>
>
> All of which are provided by freetds.
>
> I only get errors when accessing via UNIXodbc manager.
>  

No, you only have that error when accessing via ODBC from what I can
see.

The error you reported:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Shows clearly that the driver manager has located and loaded the FreeTDS

driver, and has passed the connection request to it, the error is
returned to the driver manager from the driver.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nick Gorham-2
Eric Robinson (erirobin) wrote:

> So UNIXodbc and FreeTDS both are working fine now :)
>
> My end goal was to use Oracle DG4ODBC -> UNIXodbc -> FreeTDS to access
> MSSQL from Oracle using a domain\userid and that is working fine.
>
> Apparently there is a bug in the isql on the box I am on and I wrongly
> assumed it was provided by UNIXodbc (my bad). It is not associated with
> any RPM so I have no clue what installed it, probably provided by
> RedHat.
>
> Thanks again for your responses,
>
> Eric
>  
It would have come from unixODBC originally, do you know what the bug was?

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Eric Robinson (erirobin)
I am assuming it is some bug as I could not find any setting or format
for the -U DOMIAN/UserId that would work with isql.

I have the trace files and dump files that appear to show it parsing the
domain and userid ok, but it would not connect.

I used the same exact setup with a Sql Server authenticated userid and
it worked fine, then I pointed dg4odbc to the same setup and it worked
for both Sql Server authenticated user and the network user.

I could very well have an outdated version of isql.

I would be glad to send you any addition details (trace files/sql server
logs) I can if you are interested.

If you have any ideas on what to check/change to fix the issue I am
interested as I would like to have a way to test each component along
the chain, in case something goes south in the future.

Eric

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Nick
Gorham
Sent: Thursday, April 15, 2010 5:26 PM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] domain login fails in isql but works
with tsql

Eric Robinson (erirobin) wrote:
> So UNIXodbc and FreeTDS both are working fine now :)
>
> My end goal was to use Oracle DG4ODBC -> UNIXodbc -> FreeTDS to access
> MSSQL from Oracle using a domain\userid and that is working fine.
>
> Apparently there is a bug in the isql on the box I am on and I wrongly
> assumed it was provided by UNIXodbc (my bad). It is not associated
with
> any RPM so I have no clue what installed it, probably provided by
> RedHat.
>
> Thanks again for your responses,
>
> Eric
>  
It would have come from unixODBC originally, do you know what the bug
was?

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nick Gorham-2
Eric Robinson (erirobin) wrote:

> I am assuming it is some bug as I could not find any setting or format
> for the -U DOMIAN/UserId that would work with isql.
>
> I have the trace files and dump files that appear to show it parsing the
> domain and userid ok, but it would not connect.
>
> I used the same exact setup with a Sql Server authenticated userid and
> it worked fine, then I pointed dg4odbc to the same setup and it worked
> for both Sql Server authenticated user and the network user.
>
> I could very well have an outdated version of isql.
>
> I would be glad to send you any addition details (trace files/sql server
> logs) I can if you are interested.
>  
Yep, a ODBC driver manager log would be interesting. Its not a big
issue, but I am wondering what the problem was thats fixed.

All I can think of is older isql used to pass a empty string for user
name and password if it wasn't passed on the command line.That may be
the problem here.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Eric Robinson (erirobin)
I think I may have found my issue, it appears that the password was
getting cut off at 6 characters, the full password is 8 characters and
the 7th position is a + sign.

Looks like it caused the password to get truncated and may have caused
all my pain.

[ODBC][10702][__handles.c][444]
                Exit:[SQL_SUCCESS]
                        Environment = 0x508320
[ODBC][10702][SQLAllocHandle.c][345]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x508320
[ODBC][10702][SQLAllocHandle.c][463]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x508980
[ODBC][10702][SQLConnect.c][3549]
                Entry:            
                        Connection = 0x508980            
                        Server Name = [MSSQLSRV][length = 8 (SQL_NTS)]

                        User Name = [XXXXX\xxxxxxxx.xxx][length = 18
(SQL_NTS)]            
                        Authentication = [******][length = 6 (SQL_NTS)]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE
'UCS-2LE'

                DIAG [42000] [FreeTDS][SQL Server]Login failed for user
''. The user is not associated with a trusted SQL Server connection.

                DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to
data source

[ODBC][10702][SQLConnect.c][3917]
                Exit:[SQL_ERROR]
[ODBC][10702][SQLFreeHandle.c][268]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x508980
[ODBC][10702][SQLFreeHandle.c][317]
                Exit:[SQL_SUCCESS]
[ODBC][10702][SQLFreeHandle.c][203]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x508320

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Nick
Gorham
Sent: Thursday, April 15, 2010 6:20 PM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] domain login fails in isql but works
with tsql

Eric Robinson (erirobin) wrote:
> I am assuming it is some bug as I could not find any setting or format
> for the -U DOMIAN/UserId that would work with isql.
>
> I have the trace files and dump files that appear to show it parsing
the
> domain and userid ok, but it would not connect.
>
> I used the same exact setup with a Sql Server authenticated userid and
> it worked fine, then I pointed dg4odbc to the same setup and it worked
> for both Sql Server authenticated user and the network user.
>
> I could very well have an outdated version of isql.
>
> I would be glad to send you any addition details (trace files/sql
server
> logs) I can if you are interested.
>  
Yep, a ODBC driver manager log would be interesting. Its not a big
issue, but I am wondering what the problem was thats fixed.

All I can think of is older isql used to pass a empty string for user
name and password if it wasn't passed on the command line.That may be
the problem here.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nick Gorham-2
Eric Robinson (erirobin) wrote:
> I think I may have found my issue, it appears that the password was
> getting cut off at 6 characters, the full password is 8 characters and
> the 7th position is a + sign.
>
> Looks like it caused the password to get truncated and may have caused
> all my pain.
>  
How odd, I don't know of that problem.

--
Nick

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: domain login fails in isql but works with tsql

Nani
This post has NOT been accepted by the mailing list yet.
In reply to this post by Eric Robinson (erirobin)
Hi Eric, I need your help. What is the proper set up for FreeTDS and tsql to be able to use domain login without having to provide a password to connect to db server. I saw in your post you were successful with tsql with a domain account. I have installed FreeTDS and when I tried the following command to verify and confirm the installation I am getting the following results.

Any help is much appreciated.

$ TDSVER=7.0 tsql -H myservername -p 12345 -U 'domain\user' -P '' ## without supplying a password
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18452 (severity 14, state 1) from myservername Line 1:
        "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server


But when I supply a password I am able to connect.

$ TDSVER=7.0 tsql -H myservername -p 12345 -U 'domain\user' -P 'mydomainpassword' '' ## with supplying a password
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select @@version
2> go

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1757.0 (X64)
        Jan  6 2011 20:22:23
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

(1 row affected)