Transact SQL :: Query From Database From A Different Server And Domain
May 7, 2015
In my procedure I need to pull data from a database which is on other server and domain.
As usual I have used the below query but having troubles with mode of authentication.
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=x.x.x.xsyz;Trusted_Connection=yes;','SELECT top 10 * FROM pcm.stats.visits') AS a
I get the below error:
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"
I understand from the error that the domain and mode of authentication is different.
I use windows auth and we are given sql auth on the remote server which is on different domain.
I was suggested to use 'sp_addlinkserver' to link the remote server and query the data.
I am just curious to know if we can succeed through link server, is there any script to use sql authentication of remote server in my procedure and pull the data.
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
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.
-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".
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.
I'm setting up a simple SSRS implementation for a non-profit organization, using two servers hosted at a data center. The first server has SQL Server Standard Edition and Reporting Services installed. I've designed and deployed a number of useful reports on this server.
I was hoping to isolate this first server by installing IIS and SSRS on the second server, have users browse from the Internet to that second server (over SSL, of course), and have all reports served up from databases (and, presumably, the report server database) on the first server.
During the installation of SSRS on the second server, however, I'm being prompted to specify the service account. According to the help text:
"Reporting Services. Service accounts are used to configure a report server database connection. Choose a domain user account if you want to connect to a report server database on a remote SQL Server instance. If you are using a local report server database, you can use a domain user account or Local System to run the service."
I believe I want to configure SSRS to connect to a report server database on a remote SQL Server instance; therefore, it appears that I need to enter a domain user account. The only problem is, neither server belongs to a domain; they are members of a simple two-server workgroup.
Does SSRS, configured to connect to a report server database on a remote SQL Server instance, require a domain? Does what I'm hoping to accomplish require a domain? Creating a two-server domain seems like overkill for this implementation, doesn't it?
I appreciate any comments and suggestions. Thanks!
Hi, I have been creating a web store, which we will be hosting on our Windows Server 2003 system. I am also writing a windows application that will be running on our client systems on the same network/domain. I have no need to allow internet access to the database, just within our domain. Pretty much the windows application will be running and whenever a customer places an order, the program will automatically pop-up the needed information for our credit card processing, and shipping department. So far I have been able to establish a connection to our server by creating a data connection under the server explorer. However, it does not list any databases whatsoever, the web site has been published using Visual Studio 2005 Professional on the server, and is in 100% working order. I have searched and haven't found anything concrete on this, i'm sure its just something simple that im missing. It appears that the server is definetely connected to the database (since the website is fully functional), but it is not listing the information for other systems to access it. If anyone has any ideas, please let me know. Thank you
After SQL Server 2005 Database Engine is installed by domain administrator, how to give permissions to a regular domain user so that user can control SQL Server Database service?
I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.
I have a created a snapshot and I'd like to know if there is a way for all the existing and future queries to refer to the database snapshot. Is there any way to do this?
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
Hi,I wanted to get the domain name from the email in a query..So if the email is Join Bytes!, I just to display hotmail.comThanks in advance for the help.AJ
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.
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.
I didn't know exactly which forum to post this in, so if I need to post it into another one, please let me know. Also, I have absolutely no SQL or DB experience. I am learning as I go.
The Situation: I created a virtual network for my company which included MS CRM, which uses SQL Server 2005. Everything was working fine until today. My company asked me to make a change to the domain name, as we are changing company names. I followed the processes through microsoft's documentation that I found online. The rename went fine. Since then, we can no longer access CRM. When I run the environment checks, it gets hung up on the SQL Server components. It says that it can't confirm the MSSQLSERVER service is running (it is) and it can't connect to the database. As best as I can tell, the database is looking for the old domain name, which doesn't exist. How do I edit or configure the existing database to reflect the new domain name. It's important to do this as we have data in that database that we need to access. Any help in this matter is very much appreciated!!!
I'm trying to restore database on the server in the different domain.
The destination server is located in the physically remoted place.
First I tried UNC path (including IP address) with DBCC TRACEON | OFF (1807) optioin but it didn't work.
Some Internet document suggested that the FTP file transfer & restore locally on the remote domain but I didn't use this method because there is additional step.
Instead I used the following scripts:
DECLARE @COMMANDLINE VARCHAR(500)
SET @COMMANDLINE = 'NET USE B: \192.168.1.xxxsql_backup$ password /USER:SourceServer ead_sql_backup /YES' EXEC master..xp_cmdshell @COMMANDLINE
RESTORE DATABASE [DestinationDB]
FROM DISK = N'B:SourceBackup.bak' WITH FILE = 1, NOUNLOAD , STATS = 10, REPLACE , MOVE N'dMSLog' TO N'D:MSSQLDataDatabase.ldf', MOVE N'dMSData' TO N'D:MSSQLDataDatabase.mdf'
SET @COMMANDLINE = 'NET USE B: /DELETE /YES' EXEC master..xp_cmdshell @COMMANDLINE
The script is working but it's extremly slow.
The size of database is only 1152.88 MB but it took more than 2 hours.
Is there any way that I can improve the performance?
OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]
A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?
I need to generate a script where it should check the tables of a database from one server to another server(If server 1 database XYZ has 10 tables then server 2 database XYZshould match 10 tables). If the tables matches then truncate tables data and insert the data from one server 1 database XYZ to server 2 database XYZ. If tables does not matches, then it should check and create that tables which are not matching.
When I try to connect this database from my program running on my machine , it works fine. But when i try to connect to it from the same program running on a different machine on the same domain.It gives error:
"SQL Server does not exist or access denied ."
Even when i run the same program in my machine to access the same TempDB database on a remote machine that is not in my domain, it works fine.
this is my connection string: Data Source={Database server's IP Address}TempInstance;Initial Catalog=TempDB;User ID=sa;PWD=password;
I'm trying to create a login in the default domain. I know I can pullthis information from xp_loginconfig, but don't see how I can use it inthe context of sp_grantlogin.For example, pull the domain the user is currently logged in on andinsert it into the sp_grantlogin script. Has anyone ever done this inthe past?
The below query works perfectly fine, except that it produces many outputs instead of one continuous table that can be easaly converted to xml / csv by copying it from the "Results" window.
What I need is a query that will produce a single result for all the tables in all the databases on the server.
The results: The query:
DECLARE @begin INT = 1, @end INT, @sql NVARCHAR(MAX) DECLARE @CREATE_TEMPLATE VARCHAR(MAX); DECLARE @DBNAME VARCHAR(255); DECLARE @SQL_SCRIPT VARCHAR(MAX); SELECT @end = COUNT(name) FROM sys.databases SET @CREATE_TEMPLATE = '
My Client Accidently Deleted Somes Rows From Database Now. Now We are Struggling For Almost 2 Days To Get Them Backup. I Even Checked LDF File From Notepad It Have Those Rows But Not Formatted. (Some Similar Text From Some Column Which Got Deleted.)And The Database has no Backup which i Go to Task -> Restore...Server is Running on Sql Server 2008.
I must import some exemplary file to database (MS Srrver 2000) ofcourseusing procedure Transact SQL.This file must:1.Read the xml file2. Create table3. Import this date from xml file to my databasePs. I create procedure who File xml imports to base, but unfortunately sheonly schedule when earlier create a table or table is created.So I need (Ithink) create such mini parser in language transact SQL.Does someone have some ideas?For every help Thanks==== example file xml ===========================================<root><Cust><IDosoby>1</IDosoby><Imie>Lukasz</Imie><Nazwisko>Przypadek</Nazwisko></Cust><Cust><IDosoby>2</IDosoby><Imie>Dariusz </Imie><Nazwisko>Mroz</Nazwisko></Cust><Cust><IDosoby>3</IDosoby><Imie>Tomasz</Imie><Nazwisko>Kolo</Nazwisko></Cust></root>================================================== =========--Luk
Is it possible to capture the errors which raising in System,Databases,networking using SQLServer UDB(assume DBA Database). or Is it possible to store all Errors(system,Databases,networking...Services failure...)in SQL Server UDB.
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
