This message is in reference to a previously posted SLQ Server 7 question and answer regarding the BUILTINAdministrators account (dated 11/19/2001)
Original Question: I'm using Mix-Mode Authentication. Can I remove the Builtin/Administrator loggin ID
without cause any harm to my current Login ID user(Windows NT user)? Are will
removing Builtin/Administrator what harm will this cause to my server?
Answer to above: The first thing I do after installing SQL is remove the Builtin/Administrators account for
the SQL Server. Depending on how you set SQL up, this should not affect any NT
users, unless you granted them access to the server through the local Administrators
group. This is not the prefered way of granting access, however. This will give any
administrator on the machine access to all of the data, which you may not want,
depending on the confidentiality of the data.
Situation: (SQL Server using Mixed Mode Authentication).
If the Builtin/Administrators is disabled or deleted, and the server rebooted, SQL Server will be initiated but, the SQL Server Agent won't be.
Signing onto SQL Server as SA will not be able to restart SQL Agent nor will an NT user with administrator capability have any better luck
Question: What specifically in NT and SQL Server does one need to change to get around this situation.
The SQL Server documentation is not very helpful regarding the use of this login.
While attending a SQL 2000 Administrator course, two of my colleagues were told to always delete this account but were not given any reason or explained the consequence of this action.
As default, the BUILTINAdministrators account is created during the installation of SQL Server 7.0. The default access is to have the account in the System Administrators server role which gives them dbo access to each database.
Since I don't want my network administrators to have sa privleges within SQL but still want them to be able to access the databases, I've removed them from the System Administrators server role. The SQL Server Login Properties window still shows the account having access into each of the databases as dbo, however they are unable to view or access any objects within the databases.
Shouldn't the account still have permissions, just not as sa? Can someone please explain this to me? I've checked BOL and several of the reference books I have, but don't find any detailed information on this account.
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
Hi, Are there any implications for removing this group from SQL Server logins? We are trying to tighten up security and this is one area we want to focus on?
I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated
We need to remove the BUILTINAdministrators from reporting services. I.T. personnel do not want to be able to see restricted reports. We have two department users that have been added as Content Managers and we are running reporting services under a created domain user account that we created and not a system service. I have removed BUILTINAdministrators from the Home folder and when I check, it comes right back. Do I need to deny access to BUILTINAdministrator on SQL Server 2005 itself? I would apprecate any suggestions.
After I removed the BuiltinAdministrators Group and changed the SQL Service Account from LocalSystem to my Window account this Openrowset query fail.
select a.* from openrowset('MSOLAP', 'DATASOURCE=HODB02;Initial Catalog=POS_DM;', 'SELECT {[Measures].[SALE PRICE]} ON COLUMNS, {[Item_Type].[Description].MEMBERS }ON ROWS FROM [ONLINE SALES ANALYSIS]' ) as a
OLE DB provider "MSOLAP" for linked server "(null)" returned message "The following system error occurred: No connection could be made because the target machine actively refused it. .".
Is that relate the teh Security Account Delegation?
does someone know where to find information regarding what is accesible to a BUILTINAdministrator which is not accesible to a System Administrator?
Somewhere I have read that a System Administrator can not see the "All users' folders (i.e. the collection of "My folder"s for all users of the Report Server) but I have also experienced a behaviour which has surprised me: as a System Administrator with all permissions I cann't even see folders that a BUILTINAdministrator can.
Does it mean that a System Administrator can not really manage all the resources of a Report Server?
We recently moved our Team Foundation Server from one server to another, of course the reporting services was also included in that move.
On the new server, we are not able to change Reporting Services security parameters anymore, we get this error : User or group « BUILTINAdministrators » not recognized. (rsUnknownUserName)
The old server was an english windows 2003, the new one a french version, i guess the problem is related. The BUILTINAdministrators group name on the new server is "BUILTINAdministrateurs".
Is there a way to change security params without getting this error ? How can we remove from Reporting Services this reference to BUILDTINAdministrators ? I've tried to modify the table Users directly in ReportServer DB without any success..
We have a server shared by two project teams. To tight the security, I want to remove BUILTINAdministrators off the public and sysadmin server roles. My question: any thing I should pay special attention ? I use LocalSystem to start all SQL Service. I know this is not a very good pratice yet I have no choice as our company network is a mixed of windows and novell, we do not have AD.
SQL Server 2005, Windows Server 2003: On our production db, the SA acct has been locked. I realize that I could enable mixed mode and then connect via an administrator -- but we are already running in mixed mode, and our BUILTINAdministrators account has sysadmin permissions turned off.
As it stands, the only user I can connect as does not have permission to modify any of the login info.
What recourse do I have at this point? Can I disable mixed-mode and re-enable it (via registry)? If so, will it reset the SA account or rebuild the BUILTINAdministrators login for me?
Or do I need to contact Microsoft, and if so is there anything they can do?
I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
I need to remove full admin privs from the builtinadministrator's group in report manager.
I tried removing the builtinadmin role from report manager and SQL Server, I removed it from the Site Settings area and from each individual folder's permissions.
Yet all the members of that group still have full run of the report server...
I even made a new folder that ONLY I am listed as having permissions to, yet they can see that as well...???
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account they fail with this error. " Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
I have a big problem with Reporting Services 2005 working on Windows 2003 Server. RS work as Network service, on subdomain reporting.mydomain with SSL wildcard certificate *.mydomain, Anonymous access: disabled and basic authentication: enabled ReportManager and reportServer has defualt virtual folders (/reporting, /reportserver)
My problem is: 1) I can't manage security roles and site settings with report maanger. when I try assign roles to new user or group I get followng error:
"The user or group name 'BUILTINAdministrators' is not recognized. (rsUnknownUserName) Get Online Help" when i try to execute reports in report manager, parameters controls are not displayed correctly (very simple text boxes) and I can see: The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)
and I can't see my report in browser (IE 6.0) but only export to PDF, Excel...
other functionality are working fine i.e upload new files, creatign folders....
2) Also my reportserver virtual folder does not work correctly. When I navigate to mydomain/reportserver I can see content of this virtual folder, than when I navigate to ReportService.soap i can see normal ReportServer view
reporting.mydomain - /Reportserver/
[To Parent Directory]
Montag, 10. April 2006 16:31 <dir> bin Dienstag, 6. September 2005 01:12 488278 Catalog.sql Dienstag, 6. September 2005 01:12 14738 CatalogTempDB.sql Freitag, 21. April 2006 19:45 10555 Copy of rsreportserver.config Freitag, 14. April 2006 17:29 76 global.asax Freitag, 15. Juli 2005 01:12 26582 ModelGenerationRules.smgl Montag, 10. April 2006 16:31 <dir> Pages Montag, 10. April 2006 16:31 <dir> ReportBuilder Montag, 13. Juni 2005 14:07 143 ReportExecution2005.asmx Montag, 13. Juni 2005 14:06 196337 ReportingServices.wsdl Montag, 13. Juni 2005 14:07 131 ReportService.asmx Montag, 13. Juni 2005 14:07 131 ReportService.soap Montag, 13. Juni 2005 14:07 139 ReportService2005.asmx Dienstag, 13. Juni 2006 20:01 10580 rsreportserver.config Montag, 13. Juni 2005 14:07 11845 rssrvpolicy.config Montag, 10. April 2006 16:31 <dir> Styles Freitag, 17. Juni 2005 01:09 2673 web.config
but me reports are not displayed correctly, I can run reports but top bar with parameters, export and print function are not displayed in correct format. (simple textboxes, and icons)
reporting.mydomain/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.1399.00
I think it is security issue. What schould i do to solve this problems?
I have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
Hello everyone I need some advice regarding security My security officier wants me to disable the sa account on all my SQL Servers. NT Security for the sysadmin role is already setup for all my servers for the group "Domain DBAs"
Could someone give the pros and cons. this Person wants the ability tho activate the sa account at will. (he comes from the AS400 Mind frame)
Second question are there any good books or courses that talk about securing SQL Server 7.0, 2000, etc.
I have taken over a NT 4 and SQL Server 7 system that has a NT account called SA. No one seems to know what it is for. I thought that the SA account was only a SQL login. has anyone seen this? Thanks
Scenario: My client has 4 sql boxes with applications connecting to them via various methods (ado, odbc, etc.). Some of the applications have the SA login and pwd hard coded. Too many users have the SA pwd so they want to change the pwd without affecting the applications. Well, they haven't heard of Source Safe until I got here and the projects for the applications are nowhere to be found. They don't want to hear about rewriting the applications.
Suggestions: I'm wondering if I can create an account with the same priviledges of SA and modify the SA account. I'm not sure what's possible at this point because they have taken away most of my options.
If anyone has any suggestions, I would appreciate it.
This is my first time to install the SQL 2005 on my VPC for testing purpose, I don't recall that I did configure the sa account during the instalation. Is their is any way after the installation to configure the sa account with a password? Please advice
I installed SQL Server 2005 and Visual Studio 2005 and have discovered that the ASPNET machine account was not added as a user when going into "Computer Management". I obviously need this to run ASP.net apps. I tried going to the Administrators group to add user ASPNET, but the system can't find this user. How can I install the ASPNET account?
I need to secure an sqlserver database such that it can only be accessed from an application and to prevent anyone with full admin rights on their local machine and an sqlserver licence from getting in to the database.
I am struggling with controlling access to the database from the sa account. If I attach to the database from a second instance of sqlserver which is different than that where the database was created then I am able to gain full access no problems, which is of course The Problem.
From what I can work out.
1. sa is dbo (and this cannot be changed) 2. dbo has the role of db_owner (and this cannot be changed) 3. the permissions for the db_owner role cannot be changed. 4. the password for sa is set at the level of sqlserver and not per database
.....so any sa can access any database.
I don't believe this so have to be missing something significant, any light on the subject would be gratefully received.
What is the best way of accessing a sql server on a live server? shall I use Integrated windows or use a special user account? If I use a user account, what are the needed priviledges to give it?
I seem to remember that when using VS2003 to create a website which connected to a MSDE database, I needed to explicitly grant access to the database for ASPNET machine account using the following SQL commands from within a .sql script: EXEC sp_grantlogin '<machine>ASPNET'EXEC sp_grantdbaccess '<machine>ASPNET' With VS2005, it sppears that upon creating an .MDF database in the App_Data folder this is no longer necessary. I'd be interested to know why this is so. Does VS2005 automatically do this when the database is created? If anybody could shed some light on this I'd be interested. Thanks,Wayne.
I have a DTS pkg containing VBScript scheduled as a job. The script creates an ADODB connection and opens it passing the servername, username, and password as parameters. We are using NT Authentication, and SQLAgent is using a domain account (SQLService acct).
The job runs fine if I specify a SQL username with SA authority in the ADO connection string. But if I try to use an NT acct it gets a login failure for the i.d. I also noticed that none of the NT login/users show up in the Job Owner drop down list of the job properties window.
My question is should I be able to run the job using just an NT account with SA authority or do I need both NT and SQL i.d.s to run jobs? What am I doing wrong?
The DBO account on a user database has managed to lose its SUID. This causes the account to appear in the sysusers table but not in the users collection in Ent Man. Does anyone know a way to recover that does not involve restore from backup?????
I want to remove the SQL Services account from the Local Adminstrators group of the server, so that it cannot be used to login, (using trusted sa).
I have removed from administrators in user manager, granted the account the rights to login as a service, edited the permissions on HKLMSoftwareMicrosoftMSSQLServer, HKLMSoftwareMicrosoftWindowsNTCurrentVersionPerlib , HKLMSystemCurrentControlSetServicesMSSQLServer for the account.
SQL now runs with the service account stopping and starting SQL services, but any Scheduled Tasks on the server do not run.
Any ideas what I can do to get the tasks to run, without adding the service account as an administrator on the server?
