March 30, 2004 at 2:04 pm
I have to move this weekend to active directory 2000 as my old NT4 PDc/BDC is totally out.
Do we have some articles or discussions about SQL and Active Directory?
What will be the expected SQL problems and is there a good description of what the migrating procedures should be?
I have set up the SQL with AD from the beginning but never migrated SQL servers from NT4.
Thanks a lot for any ideas.
MJ
April 1, 2004 at 7:38 am
I think there will be no problems, if you transer the Domain Users and Groups successfully
Alamir Mohamed
Alamir_mohamed@yahoo.com
April 1, 2004 at 8:05 am
Yes, this is the problem - that's what worrys me so much - there will be no one of the old users left. We have a new boss and he wants to prove himself with briliant ideas like running SQL on the NAS instead on the server, or start using Postgres instead of SQL although all the licenses are already paid, because "SQL was unstable"... Now the old system of assigning users is not longer acceptable - instead LastName_firstInitial we have to change everything to FInitialLastName...
I was thinking to create a couple of the old logins, especially the SQL domain ones, but the newtwork guys told me that this was not allowed... I have to talk to them again today.
My plan was to register SQl servers with AD, and then to change the account with the new one in the services. But that's mean that the old account should be still available.
By the way, when I open the server Properties in EM for some of my servers I could see Active Directory tab, for the other it's misssing - why?
Thanks a lot,
MJ
April 1, 2004 at 8:51 am
Your SQL Servers can run under the AD domain without registering, per se. So long as the computer itself is in the domain (or a trusted domain) Windows authentication can occur. Publishing gives you the ability to make subscriptions visible for others to find... don't know if you want (or need) to do that. Things of that sort.
Is there a trust relationship set up between the old NT 4 domain and the new AD domain?
K. Brian Kelley
@kbriankelley
April 1, 2004 at 10:54 am
No, this is the ptoblem. The old NT will be gone and the newtwork guys refuse to establish any connections to the new AD domain. They said they'll rebuild all Domain Accounts and groups, but with the different names. So, what I have to do is to log off the SQL servers and to log them into the new AD domain. In this case I have to manually add all new NT users (changing the names is such a pain) and to reestablish their permissions as they were on the old SQL servers. I also, have to change the start up account for SQL & Agent...
Is there something I'm missing?
Publishing with AD for the moment is pointless for me...
And, why AD tab is not visible on all SQL servers?
Thanks a lot,
MJ
April 1, 2004 at 11:15 am
Well, a lot depends on how security has been defined within SQL Server, especially at the database level. Are you using user-defined db roles? I hope so, because that will actually ease the transition.
Best case scenario: Windows group granted a login. Login added to database as a user. User assigned to a role. Role granted permissions.
Let's take the SQL Server itself first. You didn't say if they're running Windows authentication only or if they are running in mixed mode. If they are running Windows authentication only, make sure the BUILTIN\Administrators group has sysadmin membership (it does by default). If you're running mixed mode, make sure you know the sa passwords. You need to be able to get into the boxes once they start up. Actually, it may be a good idea to put all servers in mixed mode temporarily with a known sa password "just in case."
Ok, before they actually move the SQL Servers to the new domain... set the MSSQLSERVER and SQLSERVERAGENT services to manual. This keeps 'em from trying to start and erroring out. Once they are in the new domain, switch the start up accounts (EM ensures all registry and file permissions are set, so it's the best tool to use), start 'em, and change the services to automatic.
The security within the database can be quite complicated or it can be really simple. Let me know generally how permissions are assigned and hopefully I can offer some hints there.
K. Brian Kelley
@kbriankelley
April 1, 2004 at 11:55 am
Thanks a lot, Brian.
Oh, well, my security is not so complicated, but I have to support very old set ups...All new SQL servers work with user-defined db roles, as for me it's more easy to maintain.
On some of the servers BUILTIN\Administrators acct has been removed for security reasons.
All SQL Servers are with the mixed mode because of the old applications required that.
DB security is not so complicated eighter - In all old SQL servers I have a couple of hundreds SQL- mixed application users. Every developer has a Windows NT login, so they can connect from their local machines for development. All these logins have different permissions for the different servers and DB objects inside the DB (like some of them could exec certain SP, but not the others), and could access some DB, but not all. I do not know all about these logins and the users assigned to them as they are very old. Is there a way to script them?
On the new server there are user-defined db roles like DEV, SQLadmin, NewApps, Reporting, etc. They are corresponding to the NT Groups and very easy to maintain. Is there a way to reasign these SQL groups to the newly created NT groups without having to recreate them from scratch?
And, what about AD tab not visible on all SQL servers?
Thanks so much,
MJ
April 2, 2004 at 7:43 am
Take a look at sp_helprotect (only one p). You can use this to get the permissions back for an account, for a role, for a whole DB, etc. Throw 'em into a temporary table and then you can write some T-SQL code to generate scripts for you.
*Begin shameless plug*
I actually talk about this sort of thing in the latest issue of SQL Server Standard Magazine for March/April 2004.
*End shameless plug*
As for the new servers, if you have db roles already defined, once you grant the new Windows groups login rights to SQL Server and grant them db access, simply make them members of the appropriate roles. That's the nice thing about using user-defined db roles though they take a little extra on the initial setup.
As for the tab, you should only see it if the SQL Server's system, the computer account itself, is in an Active Directory domain. For instance, those SQL Servers in an NT 4.0 domain won't show the tab. Those in an AD domain will.
K. Brian Kelley
@kbriankelley
April 2, 2004 at 10:39 am
We are looking at a similar issue in migrating two AD forests to a 3rd new forest. Fortunately the network guys have prepopulated all accounts in the new domain/forest.
the fix from MS (lots of conversatiosn here) is to prepopulate the accounts in SQL with the new 3rd domain accounts. I.e., we have jde.com/steve on SQL Sever A. This login has matching use account steve in database master.
We will prepopulate newdomain.com/steve onto the SQL Server, same rights, then add a new user, master/steve1 with matching rights. Then when the switch occurs, we'll be ok.
The process for this from MS is to generate the script from each SQL Server, change the domain (search/replace) in the script and rerun. This doesn't work provide backward compatibility, but will work.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply