Adding a read only logins to a standby server

Hi,
I am running a standby server which backups my main Sql server. I also
use this server as readonly server and everything works fine with my
login but I no matter what I try I am not able to add new users to be
able to read from the standby server. Besides trying to add users
directly to the standby server (?which of course failed) I also tried
to add the same login on both servers allowing the new login to read
from the main database and then performed the "backup-restore"
routine.
Thanks,
RanIf you want this destination to become an available source
database, you must select the Allow database to assume
primary role check box. If this box is not selected, this
destination database will not be able to assume the source
database role in the future. If you have selected the
Allow database to assume primary role check box, you must
also specify the Transaction Log Backup Directory on the
destination database to which the logs will be backed up.
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
>--Original Message--
>Hi,
>I am running a standby server which backups my main Sql
server. I also
>use this server as readonly server and everything works
fine with my
>login but I no matter what I try I am not able to add new
users to be
>able to read from the standby server. Besides trying to
add users
>directly to the standby server (.which of course failed)
I also tried
>to add the same login on both servers allowing the new
login to read
>from the main database and then performed the "backup-
restore"
>routine.
>Thanks,
>Ran
>.
>|||Here is a totally unsupported hack to the system tables. Use at your own
risk.
This runs on the standby server. It needs a linked server path to the
primary server. Create the logins on the Primary server and give them
permissions on the databases on the primary server. Run the following
script, changing the source server and the login name. Make sure the login
does not exist on the stand-by server before running this script.
insert master.dbo.sysxlogins
([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language])
select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language]
from [PrimaryServerName].master.dbo.sysxlogins
where [name] = 'MyLoginName'
and srvid is NULL
Again, this is totally unsupported and is completely at your own risk. If
you don't understand what it is doing, don't run it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"ran" <rveierov@.hotmail.com> wrote in message
news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> Hi,
> I am running a standby server which backups my main Sql server. I also
> use this server as readonly server and everything works fine with my
> login but I no matter what I try I am not able to add new users to be
> able to read from the standby server. Besides trying to add users
> directly to the standby server (.which of course failed) I also tried
> to add the same login on both servers allowing the new login to read
> from the main database and then performed the "backup-restore"
> routine.
> Thanks,
> Ran|||Hi Koohyar,
I have no problem in making the destination become an available source
database and i don't see how this may help me solve my problem with
adding new logins to my secondary database.
thanks,
Ran
"koohyar[MSFT]" <koohyd@.online.microsoft.com> wrote in message news:<0a5701c38cea$0d3244e0$a001280a@.phx.gbl>...
> If you want this destination to become an available source
> database, you must select the Allow database to assume
> primary role check box. If this box is not selected, this
> destination database will not be able to assume the source
> database role in the future. If you have selected the
> Allow database to assume primary role check box, you must
> also specify the Transaction Log Backup Directory on the
> destination database to which the logs will be backed up.
> Koohyar
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >--Original Message--
> >Hi,
> >
> >I am running a standby server which backups my main Sql
> server. I also
> >use this server as readonly server and everything works
> fine with my
> >login but I no matter what I try I am not able to add new
> users to be
> >able to read from the standby server. Besides trying to
> add users
> >directly to the standby server (.which of course failed)
> I also tried
> >to add the same login on both servers allowing the new
> login to read
> >from the main database and then performed the "backup-
> restore"
> >routine.
> >
> >Thanks,
> >Ran
> >.
> >|||Hi Geoff,
I have tried this on my development environment, but I am keep getting
the following error message:
"Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this."
Is there anyway to get pass this restriction?
Thanks,
Ran
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> Here is a totally unsupported hack to the system tables. Use at your own
> risk.
> This runs on the standby server. It needs a linked server path to the
> primary server. Create the logins on the Primary server and give them
> permissions on the databases on the primary server. Run the following
> script, changing the source server and the login name. Make sure the login
> does not exist on the stand-by server before running this script.
> insert master.dbo.sysxlogins
> ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language])
> select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language]
> from [PrimaryServerName].master.dbo.sysxlogins
> where [name] = 'MyLoginName'
> and srvid is NULL
> Again, this is totally unsupported and is completely at your own risk. If
> you don't understand what it is doing, don't run it.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "ran" <rveierov@.hotmail.com> wrote in message
> news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > Hi,
> >
> > I am running a standby server which backups my main Sql server. I also
> > use this server as readonly server and everything works fine with my
> > login but I no matter what I try I am not able to add new users to be
> > able to read from the standby server. Besides trying to add users
> > directly to the standby server (.which of course failed) I also tried
> > to add the same login on both servers allowing the new login to read
> > from the main database and then performed the "backup-restore"
> > routine.
> >
> > Thanks,
> > Ran|||Hi Geoff,
Thanks for the tip. it worked perfectly. (my other problem was solved
with sp_configure 'allow updates',1)
Thank,
Ran
rveierov@.hotmail.com (ran) wrote in message news:<fb241b13.0310072345.76828f86@.posting.google.com>...
> Hi Geoff,
> I have tried this on my development environment, but I am keep getting
> the following error message:
> "Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
> administrator must reconfigure SQL Server to allow this."
> Is there anyway to get pass this restriction?
> Thanks,
> Ran
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> > Here is a totally unsupported hack to the system tables. Use at your own
> > risk.
> >
> > This runs on the standby server. It needs a linked server path to the
> > primary server. Create the logins on the Primary server and give them
> > permissions on the databases on the primary server. Run the following
> > script, changing the source server and the login name. Make sure the login
> > does not exist on the stand-by server before running this script.
> >
> > insert master.dbo.sysxlogins
> > ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language])
> > select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language]
> > from [PrimaryServerName].master.dbo.sysxlogins
> > where [name] = 'MyLoginName'
> > and srvid is NULL
> >
> > Again, this is totally unsupported and is completely at your own risk. If
> > you don't understand what it is doing, don't run it.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "ran" <rveierov@.hotmail.com> wrote in message
> > news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > > Hi,
> > >
> > > I am running a standby server which backups my main Sql server. I also
> > > use this server as readonly server and everything works fine with my
> > > login but I no matter what I try I am not able to add new users to be
> > > able to read from the standby server. Besides trying to add users
> > > directly to the standby server (.which of course failed) I also tried
> > > to add the same login on both servers allowing the new login to read
> > > from the main database and then performed the "backup-restore"
> > > routine.
> > >
> > > Thanks,
> > > Ransql

Adding a loggin to a server

In DMO the server object had an add method on the Logins collection. Does anyone know how this works in SMO the logins collection does not have an add.

I have spent hours on this and just cannot figure out how to create a new login on a server using SMO. I guess I could allways script the login but there must ba a way to do this in SMO.

Thanks

You can create a login as follows:

Server srv = new Server("MyServer"); //Yukon

srv.ConnectionContext.LoginSecure = true;

srv.ConnectionContext.Connect();

srv.DefaultTextMode = false;

string lgName = "MyNewLogin";

if (srv.Logins[lgName] == null)

{

Login lg = new Login(srv, lgName);

lg.LoginType = LoginType.SqlLogin;

lg.Create("MyPassword!#");

}

Peter

add sql logins from multiple domains

Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.If the domain in which your SQL Server exists and the other domains are
'trusted', then you could simply add those other domain's logins using
sp_grantlogin.
You could also create a windows group into which you add all the logins you
wanted, and grant access to this windows group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"German" <german.koninin@.crm-worldwideDOTnet> wrote in message
news:eGYi2lIaDHA.2464@.TK2MSFTNGP09.phx.gbl...
Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.

Add NT users

what's the version of your SQL Server and NT?
>--Original Message--
>After adding NT users as SQL Server logins, I tried to
set
>them up on their PC using ODBC connections but get the
>message:
>SQL Server error: 18452
>Login failed for user (null). Reason: not associated with
>a trusted SQL Server connection
>Can someone advise on basic checks?
>(Our SQL Server is setup for MIXED MODE and I myself can
>use the NT Authentication since I belong to the SQL ADMIN
>group via the main DOMAIN server setup)
>thks!
>.
>Login failed for user 'null' means that we were unable to 'impersonate' the
user. We need to be able to impersonate when we authenticate to SQL using
your Windows NT credentials.
Typical client server environment:
Scenario 1:
Client --> SQL
If this fails, then it may be a problem with the communication between the
client and the Domain Controller. You can make network traces from the
client and /or enable Kerberos logging to verify if this is the case. This
may occur when using sockets, but not with Named Pipes connections.
Scenario 2:
Web Server/SQL Environment
Client-->IIS-->SQL.
If your scenario looks like the Scenario 2 (Web Server/SQL Environment),
then this scenario is more complicated to configure.
The middle machine (IIS) must be trusted for Security Delegation. And the
Domain Admin needs to set the spn for SQL Server.
The client machine must use TCP/IP and authenticate via Kerberos
authentication. If it uses NTLM, then this will fail with "Login failed
for user 'null'".
This article goes over various scenarios:
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch05.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Add NT users

After adding NT users as SQL Server logins, I tried to set
them up on their PC using ODBC connections but get the
message:
SQL Server error: 18452
Login failed for user (null). Reason: not associated with
a trusted SQL Server connection
Can someone advise on basic checks?
(Our SQL Server is setup for MIXED MODE and I myself can
use the NT Authentication since I belong to the SQL ADMIN
group via the main DOMAIN server setup)
thks!what's the connection string look like? This error typically means SQL
Server thought you were coming in with a standard security connection...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:ecb201c40c36$87a17810$a301280a@.phx.gbl...
> After adding NT users as SQL Server logins, I tried to set
> them up on their PC using ODBC connections but get the
> message:
> SQL Server error: 18452
> Login failed for user (null). Reason: not associated with
> a trusted SQL Server connection
> Can someone advise on basic checks?
> (Our SQL Server is setup for MIXED MODE and I myself can
> use the NT Authentication since I belong to the SQL ADMIN
> group via the main DOMAIN server setup)
> thks!|||Good point - Can't remember where I last checked for this
info - can't find it in the Profiler or Performance
Logs/Alerts....any hints?

>--Original Message--
>what's the connection string look like? This error
typically means SQL
>Server thought you were coming in with a standard
security connection...
>--
>Richard Waymire, MCSE, MCDBA
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"wayne" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ecb201c40c36$87a17810$a301280a@.phx.gbl...
set
with
ADMIN
>
>.
>|||it'd be in the application code.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:c10501c40c3f$eb6ae260$a601280a@.phx.gbl...
> Good point - Can't remember where I last checked for this
> info - can't find it in the Profiler or Performance
> Logs/Alerts....any hints?
>
> typically means SQL
> security connection...
> confers no rights.
> message
> set
> with
> ADMIN|||We are not using any development tools - just settingup
the ODBC connection to enabled Access/Excel to make
connections to the SQL Server!
It is where the ODBC Admin tool (setting-up a DSN) that
has failed - any idea?
Cheers!
>--Original Message--
>it'd be in the application code.
>--
>Richard Waymire, MCSE, MCDBA
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Wayne" <anonymous@.discussions.microsoft.com> wrote in
message
>news:c10501c40c3f$eb6ae260$a601280a@.phx.gbl...
this
and
to
the
can
>
>.
>|||ODBC administrator is in the Control Panel
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||when you configure the data source in ODBC as a type for SQL Server,
clicking next on the config will bring you to the page where it asks if you
want to "With Windows NT authentication..." or "With SQL Server
authentication..." - make sure the top option is selected.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:c7f301c40cca$be4c1200$a601280a@.phx.gbl...
> We are not using any development tools - just settingup
> the ODBC connection to enabled Access/Excel to make
> connections to the SQL Server!
> It is where the ODBC Admin tool (setting-up a DSN) that
> has failed - any idea?
> Cheers!
> confers no rights.
> message
> this
> and
> to
> the
> can|||Wayne,
It may be easier to troubleshoot if you open up a case with a SQL
Support Engineer.
Login failed user 'null' could indicate problems with the DC or even DNS.
There's a utility called netdiag.exe that you can get from the WIndows
Resource kit, to validate communication with the DC and your DNS servers.
Run the following test:
isql -Snp:sqlserverNameHere -E -Q"Select @.@.version"
and
isql -Stcp:sqlserverNameHere -E -Q"Select @.@.version"
and post the results.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thks Kevin!

>--Original Message--
>Wayne,
> It may be easier to troubleshoot if you open up a case
with a SQL
>Support Engineer.
>Login failed user 'null' could indicate problems with the
DC or even DNS.
>There's a utility called netdiag.exe that you can get
from the WIndows
>Resource kit, to validate communication with the DC and
your DNS servers.
>
>Run the following test:
>isql -Snp:sqlserverNameHere -E -Q"Select @.@.version"
>and
>isql -Stcp:sqlserverNameHere -E -Q"Select @.@.version"
>and post the results.
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>

AD Security group

Hi,
I add a AD Security Group into the Server Logins but the users with in
this group cannot access the dabatase because the logins fails.
The plataform is Win2003 and Sql Server 2005.
Thanks.
RogerPlease post the exact error message, number and state that
you get for the login failures. You will get the details on
the number and state from the SQL Server error log. The
state tells you why the login fails - for security reasons,
those details are not returned to the client so you need to
check the log.
-Sue
On Thu, 12 Jul 2007 14:57:06 -0300, "Roger"
<roger@.rnospam.com> wrote:

>Hi,
>I add a AD Security Group into the Server Logins but the users with in
>this group cannot access the dabatase because the logins fails.
>The plataform is Win2003 and Sql Server 2005.
>Thanks.
>Roger
>

AD migration and SQL server logins.

Our SQL 2000 servers use Windows authentication. My question pertains to th
e
effect an AD migration has on SQL logins.
After the NT4 user accounts and groups are migrated to the AD domain and the
SQL server is upgraded to the AD domain, do the SQL logins automatically
reference the new user accounts in AD? If not do I have to manually add the
AD users and groups to SQL login and access to the databases? Or are there
tools available?You should not have to manually add the account again to SQL Server if your
network administrator choose to migrate the accounts with sid history. If
the accounts do not use sid history then you will need to add the accounts
back into SQL Server. I beleive one of the stored procedures you could use
is sp_sidmap.
"anthonyp" wrote:

> Our SQL 2000 servers use Windows authentication. My question pertains to
the
> effect an AD migration has on SQL logins.
> After the NT4 user accounts and groups are migrated to the AD domain and t
he
> SQL server is upgraded to the AD domain, do the SQL logins automatically
> reference the new user accounts in AD? If not do I have to manually add t
he
> AD users and groups to SQL login and access to the databases? Or are ther
e
> tools available?

AD Groups

Our customer is using Windows Authentication to connect to SQL 2000. They
added their Active Directory group to the SQL logins but can not connect to
SQL 2000. For a test they added the individual domain user to an SQL login
and they can connect. For some reason they can not use their AD group to
connect to SQL 2000. Then they added local admin privledge to the AD group
and they can now connect. Do you have to have local admin privledge on an A
D
group to connet to SQL 2000? How can they remove that privledge and still
connect with thier AD group SQL login'
Thanks
RyanHi,
please refer following links :
www.sqlservercentral.com/forums/shw...and-SQL-2K.html
www.flatmtn.com/computer/Linux-LDAP.html
www.windowsitpro.com/SQLServer/Arti...841/pg/2/2.html
www.openldap.org/lists/openldap-sof...0/msg00304.html
http://forums.devshed.com/archive/t...s-ms-sql-server
:-)
Regards
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Ryan" wrote:

> Our customer is using Windows Authentication to connect to SQL 2000. They
> added their Active Directory group to the SQL logins but can not connect t
o
> SQL 2000. For a test they added the individual domain user to an SQL logi
n
> and they can connect. For some reason they can not use their AD group to
> connect to SQL 2000. Then they added local admin privledge to the AD grou
p
> and they can now connect. Do you have to have local admin privledge on an
AD
> group to connet to SQL 2000? How can they remove that privledge and still
> connect with thier AD group SQL login'
> Thanks
> Ryan

Activity Monitor - host entries blank for sql login ?

The activity monitor is not showing entries for hosts which connect via sql logins (odbc), is this normal? Where can I find a list of protocols in which the hostname/ip can be picked up. We're on SQL 2005 SP1/Build 2153

Thanks

The hostname should show up if the user is connecting from a Windows box using ODBC or OLE-DB. If you are using a JDBC driver or comming from a unix machine this field is typically blank. This can be adjusted by adding the "Workstation ID" paramater to the connection string and setting it to equal the client machines host name. This object probably doesn't exist on all drivers. The string name may also be slightly different so some trial and error may be required.|||

The connections in question are from Windows OS (2000/XP) machines, no JDBC or UNIX involved......