Saturday, February 25, 2012

Cannot connect to SQL Servrer 2005 remotely using another account than sa

Hi there,
I set up an SQL Server 2005 Enterprise Edition, so it is listening on
the network.
I can login successfully from a remote machine using osql:
osql -H sqlhost -U sa
Password: XXX
1>
However I created another user "test" (SQL Auth.) and also a database
which test should be allowed to access but test cannot login remotely
(test _can_ login locally, for example in the management studio)
osql -H sqlhost -U test -d db_test
Password: XXX
Login failed for user 'test'.
What is strange about it, is that it doesn't even show up in the logs
that it did not work. There is no mention of the user "test" in the
logs when the remote login fails.
Any idea what to check? What am I doing wrong? :-(
Regards,
marcNote that you should use -S server_name\instance_name.
Perhaps you are connecting to a local instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marc" wrote:
> Hi there,
> I set up an SQL Server 2005 Enterprise Edition, so it is listening on
> the network.
> I can login successfully from a remote machine using osql:
> osql -H sqlhost -U sa
> Password: XXX
> 1>
> However I created another user "test" (SQL Auth.) and also a database
> which test should be allowed to access but test cannot login remotely
> (test _can_ login locally, for example in the management studio)
> osql -H sqlhost -U test -d db_test
> Password: XXX
> Login failed for user 'test'.
> What is strange about it, is that it doesn't even show up in the logs
> that it did not work. There is no mention of the user "test" in the
> logs when the remote login fails.
> Any idea what to check? What am I doing wrong? :-(
> Regards,
> marc
>|||When you are using 'osql -H sqlhost -U sa' without -S you are connecting to
the default instance on the local computer. Try it and run this to verify
select @.@.servername
go
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> Note that you should use -S server_name\instance_name.
> Perhaps you are connecting to a local instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marc" wrote:
> > Hi there,
> > I set up an SQL Server 2005 Enterprise Edition, so it is listening on
> > the network.
> > I can login successfully from a remote machine using osql:
> >
> > osql -H sqlhost -U sa
> > Password: XXX
> > 1>
> >
> > However I created another user "test" (SQL Auth.) and also a database
> > which test should be allowed to access but test cannot login remotely
> > (test _can_ login locally, for example in the management studio)
> >
> > osql -H sqlhost -U test -d db_test
> > Password: XXX
> > Login failed for user 'test'.
> >
> > What is strange about it, is that it doesn't even show up in the logs
> > that it did not work. There is no mention of the user "test" in the
> > logs when the remote login fails.
> >
> > Any idea what to check? What am I doing wrong? :-(
> >
> > Regards,
> > marc
> >
> >|||Yes you are right! Thanks!!

No comments:

Post a Comment