My SQL servers are using integrated windows nt security. Our user account is changing to a new domain. Is there an easy way to change the server logins to point to the new domain instead of removing the user and adding a new login from the new domain.
View 1 RepliesIs there an automatic way of changing the Domain user password getting used for running the SQL Server as a Domain user account? I'm taking about EM---Security----Domain User name and the password getting used for running the SQL Server?
View 1 Replies View RelatedHi,
We have the followoing:
-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'' ')
EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false',
@rmtuser='subDomainAdminAccnt', @rmtpassword='sunDomainAdminAccntPassword';
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".
I have a root domain and child domain.
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.
Any ideas on how to fix this ?
Hi all,I've done some sniffing around but apart from one post in thisnewsgroup haven't been able to find much on this topic.We have recently moved (well, quite a while ago but that's by-the-by)from an NT domain to an AD domain. One of our SQL Servers is stilljoined to the NT domain and, since this domain is soon to bedecommissioned, I need to join this SQL Server to the AD domain.Is there anything I should be on the lookout for WRT this move?As far as I can see, the only thing this would cause an issue withwould be permissions for "logins" etc. This shouldn't be an issue asthe only logins on this SQL Server are either local or from WindowsAuthenticated from our AD domain.Any advice would be greatly appreciated.Many thanks,Ian
View 1 Replies View Relatedhi, I am not sure what happen. but I have lost the sql mail when I changed the from one user domain to another, any sugguestions on how to solve this issue
thanks for your help
Ahmed
Hello ppl
We have a Windows server that has 64-bit SQL installed on it. It is placed in 'abc' domain right now.
Now I have to get that changed to 'xyz' domain. I know that Windows people can take care of this, but my question is do we have to worry about the SQL Server that's already installed on that server? Will it have any affect on that?
Thanks
Satya
I know it's not possible, I've read the KBs. But I don't understand why not - from my testing, it looks like the only things that break are the domain groups to which the service logins are added. The service logins can be changed, as can the IPs, and SQL starts up just fine. The only problem is the domain groups.
I saw this KB:
http://support.microsoft.com/?kbid=910708
which says this:
After you install a SQL Server 2005 failover cluster, you can change the service accounts, but you cannot change the domain groups. If you want to use different domain groups, you must uninstall and then reinstall SQL Server 2005.
But it doesn't elaborate, it just says that the groups cannot be changed. Why not? That seems silly to me - it's not just a line in a config file somewhere? Can someone please give me a good reason why the groups cannot be changed?
Hi
We have a Windows server that has 64-bit SQL installed on it. It is placed in 'abc' domain right now.
Now I have to get that changed to 'xyz' domain. I know that Windows team can take care of this, but my question is do we have to worry about the SQL Server that's already installed on that server? Will it have any affect on that?
Thanks
Satya
Greetings,
I have been tasked with changing our local domian name from .com to .local. I want to make sure I understand the risks to SQL server 2000 when i make this change. We use SQL for Great Plains version 8, here is my simple plan:
1. Dis-join all workstations from .com domain
2. Make a full backup of all databases iin SQL server 2000 -- all databases use the SA account and not NT authentication
3. Dis-join SQL server
4. Change domain name
5. Re-join SQL server box and workstations
6. Launch Great Plains and go home happy!
I am having trouble identifying the risk to my plan and am wondering if:
1. SQL will launch under the new domain
2. Will the backup I made restore under the new domian
3. Will I experience authentication problems even though we use the SA account?
I am not a DB admin and am feeling a little unsure about this task any help on the risks or links to "how to" guides would be appreciated.
Robert
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
SQL2K SP2 on Win2K Server in single native-mode domain
I'm trying to change MSSQLServer and SQLServerAgent to run under a domain account instead of LocalSystem. SQL is not running on the DC. I get Error 22042:xp_SetSQLSecurity() returned error -2147023564, 'No mapping between account names and security ID's was done'.
The SQL machine is part of the domain. I'm logged in as a Domain Admin.
What is the problem?
I need to provide a UI to get the information to add a windows login to a SqlServer database. The CREATE LOGIN Sql statment requires the user name as "DomainNameUserName". I can get a list of users in XML using the following code:
public static XmlDocument GetAllADDomainUsers(string DomainPath)
{
string domain;
XmlDocument doc = new XmlDocument();
doc.LoadXml("<users/>");
XmlElement elem;
DirectoryEntry searchRoot;
ArrayList allUsers = new ArrayList();
if (DomainPath.Length == 0)
{
DirectoryEntry entryRoot = new DirectoryEntry("LDAP://RootDSE");
domain = entryRoot.Properties["defaultNamingContext"][0].ToString();
}
else
domain = DomainPath;
searchRoot = new DirectoryEntry("LDAP://" + domain);
DirectorySearcher search = new DirectorySearcher(searchRoot);
search.Filter = "(&(objectClass=user)(objectCategory=person))";
search.PropertiesToLoad.Add("samaccountname");
search.PropertiesToLoad.Add("distinguishedname");
search.Sort.PropertyName = "samaccountname";
search.Sort.Direction = SortDirection.Ascending;
SearchResult result;
SearchResultCollection resultCol = search.FindAll();
if (resultCol != null)
{
for(int counter=0; counter < resultCol.Count; counter++)
{
result = resultCol[counter];
if (result.Properties.Contains("samaccountname"))
{
elem = doc.CreateElement("user");
doc.DocumentElement.AppendChild(elem);
elem.SetAttribute("name", (String)result.Properties["samaccountname"][0]);
elem.SetAttribute("distinguishedName", (String)result.Properties["distinguishedname"][0]);
}
}
}
return doc;
}
This works for listing the names but how do I get the NetBIOS domain name for a selected user as required by SqlServer? I have tried using TranslateName from secur32.dll. That works on some machines but for some reason on other machines, it returns a blank. Is there another way?
Thanks for your help,
Rob
Hi
Im currently working on a intranet and trying to set up some security. The intranet acesses a SQL server 2000 database. I would like to know if there is a stored procedure(or other way) of returning all the domain groups that a user belongs to when passed the users NT login. I found xp_enumgroups which returns all the groups on the domain and also xp_logininfo which returns the users of a passed domain group. These are usful but i need to just pass the NT username and return all the Domain Groups. Any thoughts, ideas would be great!
Cheers
My SQL Server is running as a Domain user account and it asked me to enter the password for thr Domain user account when I was changing the Startup account from Local to Domain user.
My QUESTION here is:
Is there a way or command for me to change the password automatically on the Services account, if I change the password for the Domain User account/.
New to SQL Server. Plan to install SQL Server 2005 standard edition on Windows 2k3. After searched a lot of places, still don't understand what exactly "domain user account" is. Could someone explain it to me?
1. Is this a OS account where SQL Server is running?
2. Or, is this an account under domain controller on other machine? Is this an account on DNS srver? How do I create it?
3. Or, is this an account in SQL Server?
Where is this account located? How do I manage it?
TIA.
How to find out whether a domain user has access to sql server or not?
Many domain groups have access to my sql server. I need to check whether a user has access to server or not.
Probably I need to check which windows group the user belongs. This looks more like an o/s question than DB. How do you guys manage this scenario?
------------------------
I think, therefore I am - Rene Descartes
Hi,
I want to use a domain user account not belonging to local admin or domain admin groups in SQL 2000/2005 Enterprise edition. This is what I've done so far..
On the machine that is the Domain Controller:
- installed SQL 2005 as a domain admin
- created a domain user account using Active Directory Users and Computers. This user is only
"Member of" domain users; not any Administrators group.
- added this user to SQL Server Management Studio->Logins and in Server Roles assigned
sysadmin role.
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.
Thanks!
Hi,
I'm new in MS SQL Server; comming from Firebird and PostgreSQL. I'm trying to import the SQL Script of a database I have in PostgreSQL 8; one basic SQL functionality is the "domain", as the way to create a user datatype; in my database I have one basic domain: OID:
CREATE DOMAIN dom_oid AS numeric(18,0) DEFAULT nextval('oid_secuence');
Is there a similar way to create this kind of types in SQL Server?
Thank you,
Guillermo
Folks,
I have read the posts concerning login failures for the ASPNET user. I have a slightly different problem, as you can see from the title of this post.
My environment is a private domain that has two machines:
1) Windows 2000 server on which SQL Server is running. Let's call it FOO_SERVER.
2) Windows XP Professional on which I am running my IIS and .NET development environment. Let's call it FOO_WORK.
3) Let's call the domain, FOO_DOMAIN.
The user, ASPNET, is not a domain user, but a local user, so I do not know how to establish it as a valid login for SQL Server. But this is beside the point, anyway, as the error I am getting has nothing to do with the ASPNET user. It appears that a different username is being used to access SQL Server.
My connection string is:
"server=FOO_SERVER;database=pubs;Trusted_Connection=yes"
The error I receive is:
"Login failed for user FOO_DOMAINFOO_WORK$"
I am using the default settings in machine.config. Specifically <processModel> userName="machine" password="AutoGenerate" </processModel>
Thanks in advance for any suggestions.
-Joel
I've been trying to workout how, without impersonation, I can allow an application running under the default ASPNET user to access a network resource (SQL Server/ADAM) not on the same physical machine.
It seems that because the ASPNET user is a local user not a domain user I can't setup windows authentication on the network resource. That seems to leave impersonation which MS et al say is unwise.
Have I mised something here?
Regards,
Steve.
Is there a way to change a logins based on domain users, we just changed domains so all the domainlogin logins are not working anymore. Do I have to reapply every security on every database object? There has to be a fix for this, its a common thing.
Any help is greatly appreciated, everything i googled applied to SQL Server 2000 and system tables that dont exist in 2005
Hello,My server is part of a W2K domain. What do you advice me as account torun my SQL*Server, service started with a domain user account or aslocal system ?I need advices from a security point of view.Thank's in advance
View 4 Replies View RelatedI m facing problem in order to publish my reports on the web. the anonymous person will not be able to see my reports, I do not why might be Reporting service restriction for anonymous person. My reports can only see by the person who are my domain user. I want to publish these reports to web. Can you please help me and tell me how to permit anonymous person to view my reports on the web out side domain
Many thanks
I have setup a SQL 2014 server with mixed authentication. Below is sequence.
1. Created a server. Added server to a domain & logged out.
2. RDP to the server using a local account. Installed SQL 2014. Kept the services to run using default NT Authority accounts during initial setup. SQL was installed in mixed mode (SQL & windows authentication). a specific 'sa' pwd was set.
3. After initial setup, I changed all SQL services Logon account to be respective domain accounts. Made sure all services restarted, up & running.
Now, for the same 'sa' SQL login account -
--> if I RDP to the server using local system admin & connect to SQL studio with 'sa' (SQL authentication) - it works.
but --> if I RDP to the server using my domain account (which is already an admin on SQL & windows), but connect SQL studio with the Same 'sa' (SQL authentication) - it fails & gives - unable to login 'sa'... ; standard error code : 18456.
Question : How can be the same 'sa' login, is acting different based on with what user context I RDP to the server ?
In an attempt to improve security for our web server/db server setup, we have a SQL Server 2005 server set for Windows authentication only and a web server (both behind a firewall together on a single local domain).
The web server is using IIS7, and the default NetworkService account to run the ASP.NET processes.
I've given the NT AUTHORITYNETWORK SERVICE account access to the database in question.
The SQL authentication fails, but the account failing is NOT the NetworkService account, but the <DOMAIN><COMPUTER>$ account.
Note that if I use identity impersonation with a specific domain account, I can get the app to work. However, I do NOT want to use impersonation, I want the default NetworkService account to work.
Does anyone know why IIS, which is supposed to be using NetworkService, is instead using the <DOMAIN><COMPUTER>$ account to connect to SQL, which is then failing?
JK
Hi
I am new to this forum so I hope I have got the right one.
The problem I am having is I installed a new server into an office with 25 users. I joined the users to the new domain with new logins.
The server process then installed outlook 2003 onto each machine. Most machines are windows 2000
Problems arose in sage I have tied this down to the report designer and odbc.
On every machine bar 1 the report designer crashed when running a report. The problem like I said was that you cannot now connect to the odbc driver for sage and when you go into odbcad and try to look at the driver you get an error saying
The setup routines for the sage line 50 odbc driver could not be loaded due to system error 127
Then after that comes
Could not load the setup or translator library
I have looked on the net for this error and it tells you to look in the registry to make sure that the odbcinst is pointing to the correct location, I have checked this and it is. I ahve also checked the security and this seems ok aswell.
The next step was I loaded a fresh copy of windows 2000 into a new folder and tried to load the odbc driver again. This failed with the same as above.
This points to the software settings but I cannot find out where.
Iwas wondering if anyone has had the same problem and they could point me in the right direction.
am working on asp application which basically is an interface to the report server. I am currently unable to determine the source of the problem so it may have nothing to do with reporting services.
View 4 Replies View RelatedI am working on asp application which basically is an interface to the report server. I am currently unable to determine the source of the problem so it may have nothing to do with reporting services.
View 11 Replies View RelatedHi,
I am trying to set up Web Synchronization using Merge Replication. The Distributor and Publisher are on the same machine. Subscriber is a windows mobile application which syncs the data using web synchronization. I have configured IIS on web server for the sync. However there is no domain user on database and web server. Is it possible set up web sync without a domain user? Which user can I then use to give permissions on the snapshot share folder.
Any help is appreciated.
Thanks
hi everybody.
Scenario
Company X has Sql server and domain x.com
so
on sql server
they have logins
XA
XB
XC
Back of database Production was done
database Production was restored in Company Y
use Production
select name from sysusers
name
----
XA
XB
XC
sp_dropuser 'XA'
Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'XA' does not exist in the current database.
How to drop 'XA' ?
I'm running my job from the command line using DTExec as follows:-
DTExec /FILE "C:MyPathMyPackage.dtsx" /CONFIGFILE "C:MyPathMyDtsConfig.xml" /MAXCONCURRENT " -1 "
When I log in as Administrator the package runs perfectly.
When I log in as Domain User (the one I really want to have running the package) I get:-
Started: 10:49:08 PM
Error: 2007-11-30 22:49:08.30
Code: 0xC0011007
Source: {807048F4-DE2A-465E-B9A7-82E163791556}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
I have checked, and the Domain User has
"Full Control" permissions to the directory the package is in and
"Full Control" permissions for the DTSX file and
"Full Control" permissions to the directory the dtsConfig is in and
"Full Control" permissions for the dtsConfig fileAny suggestions as to what is wrong?
Hi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon