Problem connecting to a MSSql server in domain

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

Problem connecting to a MSSql server in domain

Lorenzo Milesi
Hi.
I'm having troubles connecting to a MSSQL server in a network. I apologise if the question is stupid, but I've been on this for so long I don't know where other to look at.

The remote connection works fine from another Windows machine via ODBC, but I cannot make it work from Linux.
Sadly I don't have all the details of the server configuration, but I'm sure it uses Dynamic Port Allocation as port 1433 is closed...

The windows machine connects to the server using an hostname with the following syntax: IT1111111\ISYSTEM.
Other than that it uses NT Windows authentication, while I should authenticate using username and password (which I do have).
One first question: could this be a problem? My Linux machine is NOT part of the domain.

I followed the guides here [1] and here [2] with no success. I tried using IT1111111 and IP address for server name (always keeping the \ISYSTEM part) but no change, I always get a failed connection in reply. But I cannot understand exactly why the connection fails.

Configuration summary:
tds.driver.template:
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

tds.dsn.template:
[SQLMAND]
Description     = Maxxer test
Driver          = TDS
Trace           = No
Server          = 10.15.15.13/ISYSTEM
TDS_Version     = 8.0


Result:
root@docuts:~# isql -v SQLMAND userread systempassword
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect


I'm using the IP address here because the IT1111111 name is not resolvable, even by querying the Windows domain controller's DNS.
I don't know MSSQL environment very well, any help is welcome.

Thanks


[1] http://www.unixodbc.org/doc/FreeTDS.html
[2] http://lucasmanual.com/mywiki/unixODBC
--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/

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

Re: Problem connecting to a MSSql server in domain

Giovanni Marra
Hello Lorenzo,

try to set the server parameter in this way:

Server = 10.15.15.13

This parameter indicate the IP address of the machine where DBMS is installed.

Regards.



2014-04-04 9:29 GMT+02:00 Lorenzo Milesi <[hidden email]>:
Hi.
I'm having troubles connecting to a MSSQL server in a network. I apologise if the question is stupid, but I've been on this for so long I don't know where other to look at.

The remote connection works fine from another Windows machine via ODBC, but I cannot make it work from Linux.
Sadly I don't have all the details of the server configuration, but I'm sure it uses Dynamic Port Allocation as port 1433 is closed...

The windows machine connects to the server using an hostname with the following syntax: IT1111111\ISYSTEM.
Other than that it uses NT Windows authentication, while I should authenticate using username and password (which I do have).
One first question: could this be a problem? My Linux machine is NOT part of the domain.

I followed the guides here [1] and here [2] with no success. I tried using IT1111111 and IP address for server name (always keeping the \ISYSTEM part) but no change, I always get a failed connection in reply. But I cannot understand exactly why the connection fails.

Configuration summary:
tds.driver.template:
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

tds.dsn.template:
[SQLMAND]
Description     = Maxxer test
Driver          = TDS
Trace           = No
Server          = 10.15.15.13/ISYSTEM
TDS_Version     = 8.0


Result:
root@docuts:~# isql -v SQLMAND userread systempassword
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect


I'm using the IP address here because the IT1111111 name is not resolvable, even by querying the Windows domain controller's DNS.
I don't know MSSQL environment very well, any help is welcome.

Thanks


[1] http://www.unixodbc.org/doc/FreeTDS.html
[2] http://lucasmanual.com/mywiki/unixODBC
--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support



--


Dott. Giovanni Marra

Meware s.r.l.
SOA&BPM B.U. Junior TIBCO Specialist

Via della Sierra Nevada, 108 - 00144 Roma

tel.: 06/5911669 fax: 06/56561042


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

Re: Problem connecting to a MSSql server in domain

Lorenzo Milesi
> try to set the server parameter in this way:
> Server = 10.15.15.13
> This parameter indicate the IP address of the machine where DBMS is
> installed.

Thanks for the quick reply.
I've read everywhere to use SERVERNAME\INSTANCENAME so I used this schema.
Anyway I tried changing as you suggested, then issued the command
# odbcinst -i -s -f tds.dsn.template
and again:
# isql -v SQLMAND
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

:(
if you have other suggestions they're welcome, thanks

--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/

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

Re: Problem connecting to a MSSql server in domain

Matt Phipps
On Fri, Apr 4, 2014 at 6:22 AM, Lorenzo Milesi <[hidden email]> wrote:
Anyway I tried changing as you suggested, then issued the command
# odbcinst -i -s -f tds.dsn.template
and again:
# isql -v SQLMAND
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

Try connecting with just FreeTDS rather than ODBC (using the tsql command). This is a good starting point that should list all the instances on a host:
# tsql -H hostname_or_ip_here -L

Then try to connect using the other tsql options. If you can't get FreeTDS to connect either, I would continue investigating on the FreeTDS side.

-Matt Phipps

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

Re: Problem connecting to a MSSql server in domain

Lorenzo Milesi
> Try connecting with just FreeTDS rather than ODBC (using the tsql command).
> This is a good starting point that should list all the instances on a host:
> # tsql -H hostname_or_ip_here -L

This works:
     ServerName IT111111
   InstanceName INSTANCENAME
    IsClustered No
        Version 9.00.5000.00
            tcp 1223
             np \\IT111111\pipe\MSSQL$INSTANCENAME\sql\query

After this I can then connect using tsql with command:
tsql -H 10.10.10.131 -p 1223 -U user -P pass

But still I cannot connect using isql. Config summary (I specified port number for testing purposes, I hope I can remove it in the final configuration):
odbc.ini:
[SQLMAND]
Driver          = TDS
Trace           = No
Server          = 10.10.10.131
Port            = 1223
TDS_Version             = 9.0

odbcinst.ini
[TDS]
Description             = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount              = 4

freetds.conf
[SQLMAND]
        Trace           = No
        Host            = 10.10.10.131
        Port            = 1223
        TDS_Version     = 9.0


Result:
# isql -v SQLMAND user pass
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect


thanks again
--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/

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

Re: Problem connecting to a MSSql server in domain

Matt Phipps
On Mon, Apr 7, 2014 at 6:19 AM, Lorenzo Milesi <[hidden email]> wrote:
After this I can then connect using tsql with command:
tsql -H 10.10.10.131 -p 1223 -U user -P pass

But still I cannot connect using isql.

Rats, I was hoping that would help pinpoint the issue. It's good that FreeTDS is working though.
 
freetds.conf
[SQLMAND]
        Trace           = No
        Host            = 10.10.10.131
        Port            = 1223
        TDS_Version     = 9.0
 
I wouldn't worry about freetds.conf since it isn't used by UnixODBC unless you configure the DSN using the Servername option. But I wouldn't do that since it makes your configuration more complicated and probably won't solve your current issue. Just wanted to let you know that as things are, twiddling any knobs in freetds.conf won't do anything.

Besides, the odbc.ini configuration you specified should be working, and I don't know why it's not...if someone else wants to take a crack at this problem they're welcome to :) Otherwise, try getting a trace file and seeing if that gets you more information.

-Matt

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

Re: Problem connecting to a MSSql server in domain

barsalou
I remember an issue where a misplaced space character in the config  
files caused a problem, but I thought it had been addressed.

Something to consider.

Mike B.

Quoting Matt Phipps <[hidden email]>:

> On Mon, Apr 7, 2014 at 6:19 AM, Lorenzo Milesi <[hidden email]> wrote:
>
>> After this I can then connect using tsql with command:
>>  tsql -H 10.10.10.131 -p 1223 -U user -P pass
>>
>> But still I cannot connect using isql.
>
>
> Rats, I was hoping that would help pinpoint the issue. It's good that
> FreeTDS is working though.
>
>
>> freetds.conf
>> [SQLMAND]
>>         Trace           = No
>>         Host            = 10.10.10.131
>>         Port            = 1223
>>         TDS_Version     = 9.0
>>
>
> I wouldn't worry about freetds.conf since it isn't used by UnixODBC unless
> you configure the DSN using the Servername option. But I wouldn't do that
> since it makes your configuration more complicated and probably won't solve
> your current issue. Just wanted to let you know that as things are,
> twiddling any knobs in freetds.conf won't do anything.
>
> Besides, the odbc.ini configuration you specified should be working, and I
> don't know why it's not...if someone else wants to take a crack at this
> problem they're welcome to :) Otherwise, try getting a trace file and
> seeing if that gets you more information.
>
> -Matt


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

Re: Problem connecting to a MSSql server in domain

Lorenzo Milesi
In reply to this post by Matt Phipps
> I wouldn't worry about freetds.conf since it isn't used by UnixODBC unless
> you configure the DSN using the Servername option. But I wouldn't do that
> since it makes your configuration more complicated and probably won't solve
> your current issue. Just wanted to let you know that as things are,
> twiddling any knobs in freetds.conf won't do anything.

This is great, less configuration is as you say better for simplicity :)

> Besides, the odbc.ini configuration you specified should be working, and I
> don't know why it's not...if someone else wants to take a crack at this
> problem they're welcome to :) Otherwise, try getting a trace file and seeing
> if that gets you more information.

Just a note, and I'll keep on testing by myself: after changing odbc or odbcinst, should I issue any "odbcinst" command or should they just work?

Thanks again

--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/

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

Re: Problem connecting to a MSSql server in domain

Lorenzo Milesi
> Just a note, and I'll keep on testing by myself: after changing odbc or
> odbcinst, should I issue any "odbcinst" command or should they just work?

Ok, I figured out I need to execute
odbcinst -i -s -f /etc/odbc.ini
after every change to odbc.ini. My mistake. Now I CAN connect to the MSSql server! Thanks!

And it even works with "dynamic" port by changing
Server = 10.10.10.131\INSTANCENAME

thanks for the help, and forgive my newbie mistake!
--
Lorenzo Milesi - [hidden email]

YetOpen S.r.l. - http://www.yetopen.it/
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Problem connecting to a MSSql server in domain

Matt Phipps
On Wed, Apr 9, 2014 at 8:57 AM, Lorenzo Milesi <[hidden email]> wrote:
Just a note, and I'll keep on testing by myself: after changing odbc or
odbcinst, should I issue any "odbcinst" command or should they just work?

Ok, I figured out I need to execute
odbcinst -i -s -f /etc/odbc.ini
after every change to odbc.ini. My mistake. Now I CAN connect to the MSSql server! Thanks!

Wait, *that* did it? Huh...that shouldn't be necessary; we've been able to update our own /etc/odbc.ini without running odbcinst again. I'd guess there was some hard-to-see formatting problem in /etc/odbcinst.ini like Mike said, and running odbcinst again fixed it.
 
thanks for the help, and forgive my newbie mistake!

No problem! I'd call that heroic debugging though, not a mistake, since I never would have thought running odbcinst would help. I like that one-liner, too: using /etc/odbc.ini as its own template is a pretty clever way to make sure the config is tidied up.

-Matt

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

Re: Problem connecting to a MSSql server in domain

Nick Gorham-2
On 09/04/14 20:12, Matt Phipps wrote:
On Wed, Apr 9, 2014 at 8:57 AM, Lorenzo Milesi <[hidden email]> wrote:
Just a note, and I'll keep on testing by myself: after changing odbc or
odbcinst, should I issue any "odbcinst" command or should they just work?

Ok, I figured out I need to execute
odbcinst -i -s -f /etc/odbc.ini
after every change to odbc.ini. My mistake. Now I CAN connect to the MSSql server! Thanks!

Wait, *that* did it? Huh...that shouldn't be necessary; we've been able to update our own /etc/odbc.ini without running odbcinst again. I'd guess there was some hard-to-see formatting problem in /etc/odbcinst.ini like Mike said, and running odbcinst again fixed it.
 
thanks for the help, and forgive my newbie mistake!

No problem! I'd call that heroic debugging though, not a mistake, since I never would have thought running odbcinst would help. I like that one-liner, too: using /etc/odbc.ini as its own template is a pretty clever way to make sure the config is tidied up.

-Matt

I would love to know what the difference was. In theory, the code that reads it to write to the new file is the same code in the driver manager that reads the ini file. Assuming the driver was using libodbcinst to read the ini file and not doing it itself.

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