OSQL using login in a different domain

I have a server that is on one domain and I need to log into another domain.
I can do it using a standard sql login, but I'd like to do it using a domain
login in that other domain.
For example, the server is in the US domain running under a US domain
account. I want to use osql to log in to a server in the UK domain using a
UK domain account.
Can I do this? How would this look?
When I try osql -Sservername -UUK\login -Ppassword I get a message
that login failed for 'UK\login'.
Thanks,
Because of PCI security constraints, I can't create a trust for all domain
accounts. Is there any other way to do this besides the trust or using a
standard sql login?
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hello,
> You need to create a trunst relationship between domains.
> Thanks
> Hari
> "DML" wrote:

OSQL using login in a different domain

I have a server that is on one domain and I need to log into another domain.
I can do it using a standard sql login, but I'd like to do it using a domain
login in that other domain.
For example, the server is in the US domain running under a US domain
account. I want to use osql to log in to a server in the UK domain using a
UK domain account.
Can I do this? How would this look?
When I try osql -Sservername -UUK\login -Ppassword I get a message
that login failed for 'UK\login'.
Thanks,Hello,
You need to create a trunst relationship between domains.
Thanks
Hari
"DML" wrote:

> I have a server that is on one domain and I need to log into another domai
n.
> I can do it using a standard sql login, but I'd like to do it using a doma
in
> login in that other domain.
> For example, the server is in the US domain running under a US domain
> account. I want to use osql to log in to a server in the UK domain using
a
> UK domain account.
> Can I do this? How would this look?
> When I try osql -Sservername -UUK\login -Ppassword I get a messag
e
> that login failed for 'UK\login'.
> Thanks,|||Because of PCI security constraints, I can't create a trust for all domain
accounts. Is there any other way to do this besides the trust or using a
standard sql login?
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hello,
> You need to create a trunst relationship between domains.
> Thanks
> Hari
> "DML" wrote:
>

OSQL using login in a different domain

I have a server that is on one domain and I need to log into another domain.
I can do it using a standard sql login, but I'd like to do it using a domain
login in that other domain.
For example, the server is in the US domain running under a US domain
account. I want to use osql to log in to a server in the UK domain using a
UK domain account.
Can I do this? How would this look?
When I try osql -Sservername -UUK\login -Ppassword I get a message
that login failed for 'UK\login'.
Thanks,Hello,
You need to create a trunst relationship between domains.
Thanks
Hari
"DML" wrote:
> I have a server that is on one domain and I need to log into another domain.
> I can do it using a standard sql login, but I'd like to do it using a domain
> login in that other domain.
> For example, the server is in the US domain running under a US domain
> account. I want to use osql to log in to a server in the UK domain using a
> UK domain account.
> Can I do this? How would this look?
> When I try osql -Sservername -UUK\login -Ppassword I get a message
> that login failed for 'UK\login'.
> Thanks,|||Because of PCI security constraints, I can't create a trust for all domain
accounts. Is there any other way to do this besides the trust or using a
standard sql login?
"Hari Prasad" wrote:
> Hello,
> You need to create a trunst relationship between domains.
> Thanks
> Hari
> "DML" wrote:
> > I have a server that is on one domain and I need to log into another domain.
> > I can do it using a standard sql login, but I'd like to do it using a domain
> > login in that other domain.
> >
> > For example, the server is in the US domain running under a US domain
> > account. I want to use osql to log in to a server in the UK domain using a
> > UK domain account.
> >
> > Can I do this? How would this look?
> >
> > When I try osql -Sservername -UUK\login -Ppassword I get a message
> > that login failed for 'UK\login'.
> >
> > Thanks,

osql connection to server on a different domain

I would like to be able to run a script from my machine that would go out and
change the sa passwords on all SQL Server Instances on our 3 different
domains.
We have Dev, Test and Prod domains.
When I do an osql -L I only see the SQL servers on that domain.
Is there anyway I can see servers on the other domains.
e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
Any help appreciated.
MPM
Hi,
That depends up on the way your Trust relation ship is set between domains.
Please contact your system administrator.
Thanks
Hari
SQL Server MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:1280490D-08C4-424D-9202-C4F023C5236C@.microsoft.com...
>I would like to be able to run a script from my machine that would go out
>and
> change the sa passwords on all SQL Server Instances on our 3 different
> domains.
> We have Dev, Test and Prod domains.
> When I do an osql -L I only see the SQL servers on that domain.
> Is there anyway I can see servers on the other domains.
> e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
> Any help appreciated.
> MPM

osql connection to server on a different domain

I would like to be able to run a script from my machine that would go out an
d
change the sa passwords on all SQL Server Instances on our 3 different
domains.
We have Dev, Test and Prod domains.
When I do an osql -L I only see the SQL servers on that domain.
Is there anyway I can see servers on the other domains.
e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
Any help appreciated.
MPMHi,
That depends up on the way your Trust relation ship is set between domains.
Please contact your system administrator.
Thanks
Hari
SQL Server MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:1280490D-08C4-424D-9202-C4F023C5236C@.microsoft.com...
>I would like to be able to run a script from my machine that would go out
>and
> change the sa passwords on all SQL Server Instances on our 3 different
> domains.
> We have Dev, Test and Prod domains.
> When I do an osql -L I only see the SQL servers on that domain.
> Is there anyway I can see servers on the other domains.
> e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
> Any help appreciated.
> MPM

osql connection to server on a different domain

I would like to be able to run a script from my machine that would go out and
change the sa passwords on all SQL Server Instances on our 3 different
domains.
We have Dev, Test and Prod domains.
When I do an osql -L I only see the SQL servers on that domain.
Is there anyway I can see servers on the other domains.
e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
Any help appreciated.
MPMHi,
That depends up on the way your Trust relation ship is set between domains.
Please contact your system administrator.
Thanks
Hari
SQL Server MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:1280490D-08C4-424D-9202-C4F023C5236C@.microsoft.com...
>I would like to be able to run a script from my machine that would go out
>and
> change the sa passwords on all SQL Server Instances on our 3 different
> domains.
> We have Dev, Test and Prod domains.
> When I do an osql -L I only see the SQL servers on that domain.
> Is there anyway I can see servers on the other domains.
> e.g INTTEST\Testmachine is on a different domain, can I get to it in osql
> Any help appreciated.
> MPM

OS error 53 for SQL servers VPN replication?

I've got this error message when trying to create a subscriber from a
publisher through VPN in a non-trusted domain, I am using SQL server
authentication:
The schema script '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
----
--
The process could not read file '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' due to OS error 53.
(Source: ECSWEB (Agent); Error number: 0)
----
--
The network path was not found.
(Source: (OS); Error number: 53)
What is OS error 53 and how can it be fixed?This error is generally caused by the fact that the SQL Server Agent
startup account on either the publisher or subscriber is not a local
administrator on one or both of the machines.
The default location for the snapshot folder is an administrative share and
as such the SQL Server Agent acount must be a local adminsitrator to access
it. From the error I am going to presume that you are creating a pull
subscription so the SQL Server Agent startup account at the subscriber must
be a local admin on the publisher. You can verify if this is the problem by
logging on to the subscriber using the SQL Server Agent startup account and
try to map a drive to the snap shot folder on the publisher:
\\MERCURY\G$\
If that fails with the same error then you have your reason for the failure.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thanks Rand,
Yes that's right the two SQL servers are in different
non-trusted domains connected through VPN. The Distributor and Publisher SQL
Server is in the NARC domain and Agent login account as NARC\Administrator.
And the subscriber is in the HOT domain and Agent Login account is
HOT\Administrator. No trusted can be established between them. What must I
do to achieved successful pull subscription? How do I allow permissions in
the NARC domain for HOT\Administrator if there's no trusted' What about
independent SQL server logins to access shares in NARC domain(How do I set
it up if possible)?
Thanks
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> This error is generally caused by the fact that the SQL Server Agent
> startup account on either the publisher or subscriber is not a local
> administrator on one or both of the machines.
> The default location for the snapshot folder is an administrative share
and
> as such the SQL Server Agent acount must be a local adminsitrator to
access
> it. From the error I am going to presume that you are creating a pull
> subscription so the SQL Server Agent startup account at the subscriber
must
> be a local admin on the publisher. You can verify if this is the problem
by
> logging on to the subscriber using the SQL Server Agent startup account
and
> try to map a drive to the snap shot folder on the publisher:
> \\MERCURY\G$\
> If that fails with the same error then you have your reason for the
failure.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Joe,
I haven't got Rand's post available here, but I would advise to either use
SQL security or use pass-through authentication. Both are described in the
link in my earlier post.
Regards,
Paul Ibison
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
> Thanks Rand,
> Yes that's right the two SQL servers are in different
> non-trusted domains connected through VPN. The Distributor and Publisher
SQL
> Server is in the NARC domain and Agent login account as
NARC\Administrator.
> And the subscriber is in the HOT domain and Agent Login account is
> HOT\Administrator. No trusted can be established between them. What must I
> do to achieved successful pull subscription? How do I allow permissions
in
> the NARC domain for HOT\Administrator if there's no trusted' What about
> independent SQL server logins to access shares in NARC domain(How do I set
> it up if possible)?
> Thanks
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> and
> access
> must
> by
> and
> failure.
rights.
>|||Hi Paul,
Can you please give me the links again. I cannot locate your
previous post. The only problem I am having is I cannot from the Subcriber
SQL Server access the Snapshot Folder in the Distributor SQL Server because
this is two different non-trusted domains.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNYLS8$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> Joe,
> I haven't got Rand's post available here, but I would advise to either use
> SQL security or use pass-through authentication. Both are described in the
> link in my earlier post.
> Regards,
> Paul Ibison
>
> "Joe Mine" <huytuanattpgdotcomdotau> wrote in message
> news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
different
> SQL
> NARC\Administrator.
I
permissions
> in
about
set
share
problem
account
> rights.
>|||Try this article:
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted http://support.microsoft.com/?id=321822
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Orphaned SQL Logins on the Mirror

Hi there,

I’ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03.

The mirroring as all gone according to plan.

Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don’t work. I’ve read through other posts and found links to this SQL Server article:

http://support.microsoft.com/kb/918992/

This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID’s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not?

Can anyone point me in the right direction of what to do next? Or have any ideas.

Thanks

Ed

I am quite sure the problem is just like your thread title orphaned logins

If you can, failover to the mirror server, and run this to list all orphaned logins in the database you want to fix (it's per-database level, not per-server-instance)

EXEC sp_change_users_login 'report'

I imagine you just have to fix them on the mirrored servers, and then failback to the primary server again after it's fixed and working on the mirror server

|||

Thanks,

I've just run that as you said and it's not showing the SQL Login I need in the database. Can you describe how I can fix/add that user?
The sp_change_users_login 'report' stored proc, does that list the users which should be there but aren't or those which are there which are supposed to be in the DB?

The SQL Login is showing in the User group for the database but not when I run the EXEC sp_change_users_login 'report' command?

Thanks
Ed

|||

Orphaned login is where the login exist at the server level, but they are not linked to the login credentials stored inside each database

I'd say first step is to open the login under Server -> Security -> pick the login -> User Mapping

if it's all blank, it's most likely orphaned. When you try to add permission on the database, it'll give you an error <- this almost guarantees it's orphaned

Then see this thread on how to auto-fix them

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

|||

Search this forum for 'orphaned'.

|||

Hi, thanks for the help so far it’s much appreciated and very helpfull.

It appears the SP to fix the orphaned users did work; however, I now have a different problem.

The databases I am mirroring are used by our web site so in our web pages we have connection strings (and a few even use ODBC connections). In the connection strings we use an IP Address of the server (or server name) and database name/SQL Server login details.

Let’s say my principle server is 192.168.3.50 and my Mirror is 192.168.3.51 and my Witness is 192.168.3.52.

My web pages all use 192.168.3.50 as the server in their connection string, however when the database used is failed over, the active database now lives on 192.168.3.51, at this point the web page can’t find the database (as it isn’t active on the principle (192.168.3.50) any more) so reports a login failure. What I guess I need is some method for SQL server to automatically pull the data from 192.168.3.51 when 3.51 is active…. Does that make sense? So if the database on 192.168.3.50 is damaged the same server is still accessed by applications but data is pulled from 192.168.3.51 instead

If there any SQL Server mechanism I can set-up so the database continues to service requests seamlessly regardless to which server the database is currently active on?

Thanks

Ed

|||

this is off topic already, you should mark this one as answered, and create a new thread

in short, you should read more into mirroring and search a bit

other than .Net ADO which supports builtin failover server connection string, you have to handle the failover in the client side, on your own

it is beyond SQL server's scope.

We use DNS Alias so we can change the IP on the DNS without changing configurations for applications

but that means we don't have AUTOMATIC FAILOVER at all, but a manual failover with shorter down time (provided we get notified and react quickly)