SQL Security :: Domain Migration Altered SA Or Domain Admin Access To DBs
Jun 19, 2015
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any  password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
Is SQL Server sensitive to Domain group name? Like "Domain Admin"?
I have user that belong to "myDomainDomain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login.Â
-A "master domain" AD, a "sub domain" AD, a trust relationship between the two (sub trust master) -A sql server 2005 on a win server 2003 in "sub domain" AD -A linked server to "sub domain" AD -A linked server login using a "sub domain" admin acccount -A view to this linked server -A grant on masterDomain/Domain Users to the database -A grant on subDomain/Domain Users to the database -We want all connections done through "Windows Authentication" not "Database Authentication".
Queries on the view work fine using "sub domain" user accounts. Queries on the view fail using "master domain" user accounts (including master domain admin accounts)
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."
All connections are done through "Windows Authentication" not "Database Authentication".
Can we establish cross domain connectivity with "Windows Authentication" ?
Below are details of the implementation:
SELECT TOP (100) PERCENT * FROM OPENQUERY(ADSI, 'SELECT displayname, givenName, sn, cn (etc...) FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')
In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:
"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'
Error: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT displayname, givenName, sn, cn
FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'
WHERE
objectCategory = 'Person'
AND objectClass = 'user'
" against OLE DB provider "ADsDSOObject" for linked server "ADSI".
We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success. - Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server ------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
We have a network setup with two domain controllers, DC1 and DC2, working independently from eachother along with a DBserver1 that runs a BCM database and is a member of DC1. For certain reasons we would like to demote the DBserver1 and join it on the domain of DC2. What are the steps required in order to properly move a BCM Database running on SQL2005 to a new domain, where the security data lies in the active directory of DC1?
I am trying to migrate users accounts from a 2000 Server to 2003 server I am changing domain names and the new domain a is a child domain off a parent domain. I have created a trust on both domain servers and the parent domain and I have created administrative users on each domain I am using the ADMIT migration tool and can get all the way through it then get an access denied when it trys to create the accounts. The knowledge bases on this say I need a Domain Admin user on each domains for the other domain. Being in a child domain it does not let me create this I have created users and added the admistrative group for each user this should give the rights to create the users on the new domain, but still am getting the access denied.
Does anyone know what I am missing on this? Any help would be greatly appreciated.
Here's the situation we're facing. Our organization is going to consolidate into a single domain. The plan of action is to disband the old domain, and create an all new domain. Most of our servers will also be renamed in the process.
We've got something of a two-server setup, with one machine handling SQL Server, and the other hosting various intranet sites, including Reporting Services 2000.
SQL Server itself shouldn't be a huge problem to migrate - the machine only has about half a dozen Windows logins set up. In my experience, though, Reporting Services is a much more fragile animal. Just getting it up and running on Win2003 SP1 and coexisting with ASP.NET 2.0 was enough of a stunt already. What will I have to watch out for to make sure this doesn't completely hose our installation? Would it be too optimistic for me to hope that I'll just have to reconcile any domain service accounts, change the connection string for the report server catalog database, and be done with it?
We have a problem with our RS2005 after migration into another domain.
The problem is that we have got duplicate access rights for the same AD usergroup. When trying to remove or add a new AD group we get the error msg - "... role assignment is not valid. The role assignment is either empty or it specifies a user or group name that is already used in an existing role assignment for the current item. (rsInvalidPolicyDefinition)"
Books online gives no answers and neither does other serach engines .....
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
Hi , We are using SBS2000 with SQL 2000 and Terminal server . In the Terminal server ,we have an application that connect to sbs (sql) . The Problem is that User without Domain Admin permission can not modify in database. How Is it possible to grant full access to SQL2000 without giving users domain admin access?
is it possible to deactivate the groups admins and domain-admins in sql server without getting in trouble with the sql-server. For example when the system boots the program should start normally without any problems.
We want do deactivate the accounts because we have some critical information in sql server and dont want to give all admins the possibility to have a look at these data.
I have a server that belongs to domain 'a'. The server is neither a PDC or BDC. This server has SQL Server 7 installed. I wanted security set up so the Domain Administrator could select/update rows in the database and administer the database as well as the local administrator of the Sql Server. From a workstation the domain administrator can create tables but cannot insert rows. From the Server in question the domain administrator can create tables and insert rows. Why does it make a different what box the domain adminstator logs on to?
We've encountered a problem on one of our SQL servers running integrated security where MS Security Manager errors out with "An error occured executing sp_addlogin using Domain_nameusername - " is not a valid name since itbegins with an invalid character." We think it is because the domain has the underscore character in it name. Can anyone confirm or point to other possible configuration issues?
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases. We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
Server: Windows 2003, SQL 2005, on a domain Client: Windows 2008 Beta, not on any domain
I created an account with the same user name as the domain user on the client machine. And then I logged in as that user and went to Manage Network Password. I entered the correct domain credentials. Verified that this worked for file shares. However, SQL does not appear to be recognizing this and it tells me:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I have verified that this domain account is working properly with SQL when the client is also on the domain.
How can I get this Windows authentication scenario to work where the client is not on the domain and the SQL server is on the domain?
Does anybody know if it is possible to establish a connection to an sql express instance only with integrated security when this express instance is running on XP which is NOT part of a domain?
I am running into a weird issue with a new SQL Reporting Services 2014 server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup, however I am running a similar configuration with SQL Reporting Services 2008 on Windows Server 2008 R2 and do not run into the same errors.
The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.
Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name, ECTSTSQLRS5, and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:
DNS/URL used: testprjmnmtreports14.company.com Server Name (FQDN): ECTSTSQLRS5.company.int AD Domain Name: company.int Server Version: Windows Server 2012 R2 AD Functional Level: 2008 R2
As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?
I have DBA that is convinced that they need domain admin rights to install SQL 2005 into an existing cluster. The domain groups and service accounts for SQL have been created already. Is having domain admin rights required during the install of SQL 2005 in a cluster?
Is there a way to access a SQL Server running on a different domain? I can access the same SQL Server from my machine, which is on a different domain using the ODBC connection, but when I try to access it using an application written in VB6.0 then I get the SQL Server does not exist error. I'm using the SQL Authentication method.
I am seeing a couple of domain/username accounts trying to access SQL 2k5 SP2 and get the error above. The concern I have is these accounts shouldn't be trying to access SQL at all and do not exist is SQL hence the error The question I have is how can I track down what is trying to use this account and connect to sql? Thanks in advance.
John
SQL Server Log:
Message Login failed for user 'DOMAIN ampbell'. [CLIENT: <named pipe>]
We recently upgraded to SQL Server 2005, we've added SSRS to same server. This server is an internal SQL server. I can access to the reports from http://localhost and http://servername/ but how do I access reports I've created from outside of the domain? Does SSRS needed to be installed on an server with IIS and SQL Server 2005 that we can get to from outside? Please help! Thanks.
I want to give a client access to a SQL Server 7 database sitting on a different NT domain without setting up a trust relationship between the two domains. Has anyone tried doing this?
I'm trying to access an SQL server 2005 database over the network. I'm at a client location plugged into their network, but when I log into my laptop I'm not logging into their domain. I have to access their network by typing in the name and password they gave me.
I cannot seem to access the database from my computer. I try to create an ODBC data source in the administrative tools, but the drop down list of detected SQL servers does not show the server I am trying to connect to. The weird thing is, it does show many other SQL servers on their network... just not the one that I'm trying to connect to. And I know that the one I want to connect to is working correctly because if I remote desktop into one of their machines (which is logged onto their domain), I can see it fine in the drop down.
Does anybody know how I can get a connection to this database from my computer, even though I'm not on the domain?
This is my first time using the forums here nor am I a SQL programmer. I'm trying to help a coworker figure out how to access files from a network share (using a UNC path) from a different domain. Right now we have three domains - Production, Development, and the Local. There is a one-way trust setup with the development and local domains, so using cross domain accounts is easy. Unfortunately, we cannot setup one right now with our production domain to any of the other two domains. Is there a way to pass production credentials from our local or development domain servers to a production server share? Am I even approaching this in the right way? Maybe there is a different method, any help would be GREATLY appreciated! -Andrew Network Administrator
I have a quick question regarding domains and workgroups.
Currently I am working on an issue in the office of a small business. Right now there are 3 client computers that connect to a dell server running windows std. server 2003. The server has sql running on it that takes care of the invoicing system. Two out of the three work stations are able to use the database fine, but there is one that is unable to connect to the database. The only different that I could find is that the two workstations that DO work are currently set to use a workgroup, whereas the one workstation that does NOT work is set to use a domain...... I tried switching that computer to workgroup, but then I was unable to login as the normal user that I had always logged in as before.....
What can I do to solve this dilemma?
Any help would be greatly appreciated!
I apologize if this is the wrong forum for this, and if it is, if someone would point me to the correct one I would appreciate it.
I do not know if this is the correct area to post this topic? So, How to access different sql server with query analyzer? Usually, when to install sql server, it access the database server locally installed, now I like to access other sql server within a domain using query analyzer. How to configure this in order I could use query analyzer to access other sql server within a domain? Thanks in advanced.
We have purchased an ERP system from a vendor which uses system DSN for all the reports. The system automatically creates DSN with Sa with SQL Server. The problem is the DSN is not working with AD users.
Active Directory server: Windows Server 2008 32 Bit.
SQL Server: Windows Server 2012 64 Bit. This server is already member of my Domain. e.g. CompDomain.com
What should I need to do in client PCs or Server to avail ODBC to AD users.
Hi all,it happen to me a strange problem:i have a mdb file (in Access 2K) with SQL Server 2K linked tables whoruns on a workstation which is on a different domain that the SQLServer. It works.If i create a mdb file from a workstation which is a the domain of theSQL Server and then i run it a my non-domain workstation i have errormessage:Login failed for user '(null)'. Reason: Not associated with a trustedSQL Server connectionBut if i reattached my tables it works.If someone have an idea....PS: same ODBC on both machines