SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Administration and Powershell


Administration and Powershell

Author
Message
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 134
Hi,

I have a script I am writing to make life easier for myself around requests for new Database environments by our developers. (Dev/Test/Live).

All was going well until I stumbled on a little "issue-et". I can create my db and users on one SQL instance Fine.

When I created a loop to do this for each instance the second time around the loop I get the following error. from this line *** $SQLuser.Create("123456789Ac") **

Exception calling "Create" with "1" argument(s): "Create failed for Login 'XXzzx2314gsrdf_user'. "
At S:\Al\Scripts\PS script\New Database\CreateNewDatabase.ps1:108 char:16
+ $SQLuser.Create <<<< ("123456789Ac")
+ CategoryInfo : NotSpecified: (Smile [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException



An extract of my code below

$DevServer = "MSSQL\DEV"
$TestServer = "MSSQL\TEST"
$LiveServer = "MSSQL\LIVE"
$AllServers = $DevServer,$TestServer,$LiveServer

.....get db name and user details here....

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

foreach ($server in $AllServers)
{
$sqlSrv = New-Object Microsoft.SqlServer.Management.Smo.Server ($server)
$SQLLoginname = $DatabaseName + "_user"
$SQLuser = new-object Microsoft.SqlServer.Management.Smo.Login -ArgumentList ($sqlSrv,$SQLLoginname)
$SQLuser.Name = $SQLLoginname
$SQLuser.LoginType = "SQLLogin"
$SQLuser.PasswordExpirationEnabled = $false
$SQLuser.DefaultDatabase = $DatabaseName
$SQLuser.PasswordPolicyEnforced = $false
$SQLuser.Create("123456789Ac")

# Windows Group
$SQLLoginname = "MyDomain\$ADSQLGroup"
$WGuser = new-object Microsoft.SqlServer.Management.Smo.Login -ArgumentList ($sqlSrv,$SQLLoginname)
$WGuser.Name = $SQLLoginname
$WGuser.LoginType = "WindowsGroup"
$WGuser.DefaultDatabase = $DatabaseName
$WGuser.Create()

Remove-Variable -name sqlSrv
Remove-Variable -name SQLuser
Remove-Variable -name WGuser
}

Any help I can get on why I get the error would be great. (No alternative suggestions please) :-D
bruce 1565
bruce 1565
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1028
That's an odd one. The code looks like what I have in my SQL Server installer script and you say it works on the first server.
I guess it's time to set up a test somewhere. One question before I do that. Are you sure all three of those servers are in Mixed Authentication mode? Someone changed one of our development servers a while back without letting me know.
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 134
Thank you for looking. Yes, all three servers are in mixed Authentication mode.

Thanks
Taggs
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 134
The plot thickens.

For a test, I thought I would remove the loop and add all three servers in manually (one at a time) to see if this works. Now my section "windows group" is failing with the same error!

This was working before I put it in the loop and it worked first time around the loop! Crazy
bruce 1565
bruce 1565
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1028
I didn't do the create database part but testing the Powershell from your OP works for me with only one change.
I hardcoded the DefaultDatabase for both logins to msdb. I initially had $Database = 'bkTest' but that database only existed on 2 of the 3 servers I was testing against. If the database you're setting as default doesn't exist you'll get that create() error.
Are you waiting to add the logins until after creating the databases?
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 134
Yes, creating the security will come next.

My plan is
Create AD groups
Created DB's
Create users
Set security
Created secuirty scripts
Created DB clone scipts (ie live -> Test etc)
Email Backup guys (we use Networker not SQL backups)
Email details to requesting user

Sit back and relax Wink

One further advancement I have found is it may be a timing issue. I have put a wait before the AD group creation in SQL and now it passes (sometimes - more often than not). But that raises the question (why didn't it need the pause in the loop?)

Crazy
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 134
Ploblem solved? I'm not sure!!!

I've tested my script a couple of times now and it has worked OK. I've added a few sleeps into the script and that seems to have done the trick. One after the AD Group creation (maybe the group was created on another DC than that used by the SQL box to add the group?), the second after DB creation and alter (maybe due to slow disks still configuring the db files?).

Anyway it is working for now and its time to move on to the next bit (and break it again).

Thanks again
bruce 1565
bruce 1565
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1028
That makes sense. I’ve seen AD groups created or modified but not seen the addition/change for almost 5 minutes until it finally propagated to other controllers.
So you’re welcome but I didn’t do anything but test what you’d already done.
Good luck with the rest.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search