EXEC'ing on SQL Server 2008 from 2000

[Guillaume Filion]

Hi,

I'm in the process of moving our database from SQL Server 2000 to a
new server running SQL Server 2008. During the transition, I need to
EXEC SQL commands on the new server from the old one.

On my WinXP workstation, I can run this command without a hitch:
isql -E -S NEWSERVER -d SANITIZED -Q "EXEC importation_FI"

However, running the same command on our old 2000 server gives me this
error:
isql -E -S NEWSERVER -d SANITIZED -Q "EXEC importation_FI"
Msg 18452, Level 14, State 1, Server MAESTROII, Line 1
+chec de la connexion. La connexion provient d'un domaine non approuvÚ
et ne
peut pas Ûtre utilisÚe avec l'authentification Windows.
DB-Library: Login incorrect.

Both isql are version 2000.80.194.0.

I was suspecting that the trusted connection was not working on the
old server so I made a mapped drive to the new server, but that didn't
change a thing. After an afternoon of googling for the solution, I'm
stuck! :( Hopefully one of you might be able to point me in the right
direction...

Thanks a lot,
GFK's

[Erland Sommarskog]

Guillaume Filion (gfi...@gmail.com) writes:
> I'm in the process of moving our database from SQL Server 2000 to a
> new server running SQL Server 2008. During the transition, I need to
> EXEC SQL commands on the new server from the old one.
>
> On my WinXP workstation, I can run this command without a hitch:
> isql -E -S NEWSERVER -d SANITIZED -Q "EXEC importation_FI"
>
> However, running the same command on our old 2000 server gives me this
> error:
> isql -E -S NEWSERVER -d SANITIZED -Q "EXEC importation_FI"
> Msg 18452, Level 14, State 1, Server MAESTROII, Line 1

> +chec de la connexion. La connexion provient d'un domaine non approuv�
> et ne
> peut pas �tre utilis�e avec l'authentification Windows.

> DB-Library: Login incorrect.
>
> Both isql are version 2000.80.194.0.
>
> I was suspecting that the trusted connection was not working on the
> old server so I made a mapped drive to the new server, but that didn't
> change a thing. After an afternoon of googling for the solution, I'm
> stuck! :( Hopefully one of you might be able to point me in the right
> direction...

First of all, try OSQL or SQLCMD rather than ISQL, since ISQL uses DB-
Library which is not the modern client API.

Next, it is difficult to diagnose from here why Windows authentication
does not work. Obviously it has something to do with how your environment
is set up.

Do you have a domain or a workgroup?
Which operating system do you have on the servers?
Is any of the machine a domain conrtoller?
Are the machines in the same domain?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Guillaume Filion]

On 6 nov, 18:24, Erland Sommarskog <esq...@sommarskog.se> wrote:
> First of all, try OSQL or SQLCMD rather than ISQL, since ISQL uses DB-
> Library which is not the modern client API.

Ok, thanks, I'll use osql, since sqlcmd is not present on the Win2k
Server.

> Next, it is difficult to diagnose from here why Windows authentication
> does not work. Obviously it has something to do with how your environment
> is set up.
>
> Do you have a domain or a workgroup?
> Which operating system do you have on the servers?
> Is any of the machine a domain conrtoller?
> Are the machines in the same domain?

The old server is running Windows 2000 Server SP4 Small business
server. It's a domain controller for sracq.intra.

The new server is running Windows 2008 Server Enterprise SP2. It's a
member of the workgroup SRACQ.

I'm not sure why the old server is a domain controller since none of
the computers on the network use it... That may be the source of the
problem, but I'm not sure how to solve it.

Thanks a lot for your help,
GFK's

[Erland Sommarskog]

Guillaume Filion (gfi...@gmail.com) writes:
> The old server is running Windows 2000 Server SP4 Small business
> server. It's a domain controller for sracq.intra.
>
> The new server is running Windows 2008 Server Enterprise SP2. It's a
> member of the workgroup SRACQ.

Thus, if you are logged in as DOMAIN\USER and try to connect to an
SQL Server instance which is not part of the domain, I would not expect
integrated security to succeed. Overall, integrated security in a workgroup
is difficult. The passwords must match. There is also a group-policy setting
that must permit it. (I don't remember that setting now.)

I would suggest that you either join the server to the domain, or that you
use SQL Server authentication.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx