| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-09-14 : 16:41:14
|
I have an interactive Windows Active Directory domain login to a Sql Server on a remote server. I am tasked with creating databases on it that can link across the network to other SQL Servers around the network. I created a test server on my local machine, set up linked servers, etc. and it works.Now, I am being asked "what type" of account (or login) do I need for this server to do its thing, run jobs, and access other servers, without me being at the keyboard launching everything.This is how I answered, but I still don't know if this is enough. If someone could explain to me exactly what to ask for, it would happen quicker and be right: quote:
About account - I am not really that versed in "Windows Active Directory accounts", but as far as I know, it is a service type account not associated to an employee, preferably with a non-expiring password that allows the database (on its own) to log into other databases AS IF it were a person logging in from there. It will login from the database - not from a keyboard with a person behind it. And it will stay there even after I am gone - in other words, not tied to me. If I can come up with more detail, or if they need more detail, I am still looking at some things now. We are talking about a domain account, not just one on the machine.
Duane |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-14 : 16:49:50
|
| Thank you so much for the rapid response. Do you know of a place I can read up more on this subject? What I have seen in my research is somewhat confusing and unclear. There's Windows logins, domain logins, computer logins, sql server logins, etc. I DO understand your response, but the whole discussion gets very murky. And then there are the different services that the computer runs to run SQL Server. Do those services actually 'log in' somehow as well? I am trying to understand the architecture better.Duane |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-14 : 16:58:34
|
| It really depends on the application. If you want SQL Server to launch jobs that access other servers, etc... then you typically setup the SQL Server service with a domain account that has permissions to do the things on the other server. We setup all of our database servers to use one service account. This service account has local admin on all of our database servers. And yes the service account does login as that's what services do (you'll see a login as type field in the services applet). Your question is best answered by Windows administrators though as I'm only using the terminology that I've heard. There could be a different name in the actual AD.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-16 : 11:01:54
|
| I have just been asked by the people that create the domain accounts as to what roles they said I needed and they gave me a list of server roles. I just said public and bulkadmin - I didn't see any purpose in giving it other roles. Is that generally correct? I cannot and don't need to do anything else but read data from those servers and probably some bulk copying from the other servers (hence I chose bulkadmin. Then, I was asked if I wanted an application role. I said no to that but I am not sure it's correct because SQL Server IS an application, I guess. If someone could help me understand this, I will at least have peace with my request, or at most, ask for corrections.Thanks again.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-16 : 14:53:30
|
| Actually, I don't even know what they are asking because I don't know much about Active Directory. But I do have sysadmin on the server and am dbowner on the database I am creating. As far as I know I can assign all this on SQL Server. So, I don't know if they are asking for anything more than what appears to be server roles in the above post.Anyway, thanks again for helping.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-16 : 16:39:51
|
| Actually, I may not have been completely clear in what I said. My personal login is sysadmin. The one I am having created as a faceless service account (that runs when I am not here) is what they are asking me about. I have local admin personally but this new service account won't. So, to review:My personal login has local admin on the box.My personal login gives my sysadmin on the sql server.I am the dbowner.The service account must access other sql servers on the domain(s) to read data (not update) or bulk copy from.I know I can personally grant these permissions/roles as far as SQL Server is concerned.I guess it comes down to the fact that the Windows Active Directory people have to know how to create the Windows domain account. I don't know the parameters they are asking for or what comprises a Windows account. When they asked me about roles (apparently granted in SQL Server), I don't know if they just wanted it for their own records, or if there is a higher hierarchical role established that would need to be given to this new service account or what. Or maybe THEY don't know what they are asking. But I don't know enough to question it. Thanks for all the effort here. I may just have to wait it out and see what they come up with.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-16 : 16:43:04
|
| All they need to do is create you a service account where the password never expires. You then add this user to your database servers and grant the access. The AD team does not do anything else. The account needs bulkadmin and db_datareader, which they can put in their records. The account will also need read access to the directory where the files will be located for the bulk imports, but typically a Windows administrator does that and not an AD admin (they could be the same person though if your company isn't big).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-16 : 17:16:27
|
| Thank you so much. It is getting much more clear now. One last question, please - Do I or does someone have to add this service account as a login on the box itself or just on the SQL Server itself (in the logins)?Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-09-16 : 17:39:08
|
| So if I do bulk imports, and reads through SQL Server, then I only need the login added to SQL Server logins, but if I need to get a file from another folder, probably through ftp or if I bcp it to the sql server from the command prompt or run a xp_cmdshell, I suppose I need it added to the box as well. At least that is how I am finally sizing this up. Well, I will start by adding the account to SQL Server logins. Thanks once again.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
What type of windows domain log-in do I need?