SQL Remote Connection Problems To A Computer On A Domain - Obviously I Am Missing Something...
Apr 6, 2008
Hi all. I am a bit new to SQL but I have studied much info, BOL, various best practices and so on. The problem is - I cannot find any tutorial which could tell me step by step how to set SQL server on a PC and connect to it from another computer (they all are on a domain). So I decided that I am doing something wrong...
I have done the following (all is done on virtual PCs):
1.I have installed a virtual PC with windows 2003, which acts as a domain controller.
2.I installed Win2003 on a second PC and added it to the domain. Installed MS SQL Server 2005 and SP2 on it. Server uses Windows auth. and all the services are running from a dedicated domain account which I added on the first PC.
I added a domain account (let's call it USER_X) to the SQL server Security/Logins
Everything is OK locally, I can connect to the server using USER_X from the same PC.
3.I installed Win2003 on a second PC and added it to the domain. Installed MS SQL Server 2005 client componenst, SSMS and SP2 on it. Logged on as USER_X. And tried to connect to the PC number 2. And failed. SSMS gives an error (1396) Named pipes error "Could not open a connection to SQL server". But the server appears on the list if I click Server name->Browse for more->Network servers. Why I cannot connect to it if I see it?
Tried to enable Windows and SQL auth, created a pure SQL user USER_Y, checked locally - ok, can connect. But cannot connect as USER_Y from the third PC.
Finally
4.I installed Win2003 on a fourth PC (did not add it to the domain). Installed MS SQL Server 2005 and SP2 on it. Server uses Windows and SQL auth. Enabled Windows and SQL auth, again created a pure SQL user USER_Y, checked locally - ok, can connect. And checked connection from the third PC - I can connect fine as SQL USER_Y to the SQL server on a PC which is not on the domain but cannot connect as SQL USER_Y to the SQL server on a PC which is on the domain!
What am I doing wrong? How to connect from a PC on a domain to another SQL server on the same domain? It is just a basic config which (I think) SQL admins have gone through many times but I am stuck.
Help, please! I hope to hear from someone who has installed SQL server on a domain and successfully created remote connections.
Thanks.
View 5 Replies
ADVERTISEMENT
Oct 19, 2006
Hi,
I have installed SQL SERVER 2005 on my network machine.
How do I connect the DB from my machine.
From the Microsoft SQL server DSN CONFIGURATION PAGE on my machine,
I put in the name of my network PC and the SQLEXPRESS .
But connection FAILS.
The Server Name on my Network PC : RCH1SQLEXPRESS
The Full computer name is RCH1
So in my DSN CONFIGURATION PAGE on my machine,
I enetered as RCH1SQLEXPRESS
What am I doing wrong?
View 3 Replies
View Related
Jun 3, 2008
plz help me...
when establishing a connection to the server. when connecting to server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections.(provider: Named pipes provider, error:40 - could not open a connection to sqlserver)
View 2 Replies
View Related
Feb 20, 2007
I am trying to set up a ODBC connection to a remote database using the ODBC data source administrator. I am getting an error message complaining of an invalid instance(). The database I am trying to connect to does have a default instance and a named instance. Do I need to specify the instance name somewhere in the connection settings? I tried specifying the server name as <Remote computer IP Address>/<Instance Name> but this didn't work. Any ideas?
View 4 Replies
View Related
Apr 7, 2008
Sorry I didn't know the best forum for this question.
I have 3 SQL 2005 x64 servers on my remote domain.
10.1.10.65 is the primary
10.1.10.66 is the mirror
10.1.10.67 is the whitness.
All 3 machines run the MSSQLSERVER service as a domain admin user acount, and all of the other servies use LocalSystem.
All machines are configured to use the default instance name of MSSQLSERVER.
I have another seperate server ON THAT DOMAIN which runs IIS and ASP Classic.
I installed the SQL Native client on that machine.
Here is the ASP code:
-----------
<%
dim strConnString, objConn, rs, sql
strConnString = "Provider=SQLNCLI;Persist Security Info=False;User ID=sa;password=MyPassword;Initial Catalog=LCR;Data Source=10.1.10.65; Failover Partner=10.1.10.66"
sql = "SELECT * FROM DFW"
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = strConnString
objConn.CommandTimeout = 300
objConn.Open
SET rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sql, objConn
Do While Not rs.EOF
Response.Write rs(0) & "
"
rs.MoveNext
Loop
rs.close
objConn.close
set objConn = nothing
set rs = nothing
%>
--------------------------------
If I run this code on the server on the SAME domain and shutdown MSSQL services on the primary machine the Failover server starts working an serving data requests.
It I then take that code and run it from another REOMTE domain, I can connect to the primary server and get data, but when I force a failover for testing it refuses to connect the the MIRROR server.
In the error log on the primary server I see 12 errors.
There are 4 sets of the following 3 errors, as SQL will try to connect 4 times before failing.
1)
Date 4/7/2008 12:00:39 PM
Log SQL Server (Current - 4/7/2008 12:00:00 PM)
Source Logon
Message
Login succeeded for user 'sa'. Connection: non-trusted. [CLIENT: 10.1.3.79]
2)
Date 4/7/2008 12:00:39 PM
Log SQL Server (Current - 4/7/2008 12:00:00 PM)
Source Logon
Message
Error: 18456, Severity: 14, State: 16.
3)
Date 4/7/2008 12:00:39 PM
Log SQL Server (Current - 4/7/2008 12:00:00 PM)
Source Logon
Message
Login failed for user 'sa'. [CLIENT: 10.1.3.79]
Why does this work when the code runs on the LOCAL domain only when I'm connecting via SQL Security?
Thanks for any help.
John Rossitter
View 3 Replies
View Related
Feb 16, 2007
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
View 8 Replies
View Related
Mar 28, 2008
I am still trying to figure some last issues before installing sql 2005 standard edition.
vista issues, sql 2005 sql sp2 etc
My question is simple actually, It is about configuring administrative accounts on my computer.
I wish to use configure the administrative account?
I wish to use a third party in deploying sql reporting services
Do I need to set up a local account in report manager?
the article in setting up reporting services in vista
uses domain/instance name or computer name/instance name
how do I do correctly set this up? I will be using july 2007 dvd for
sp2 ( I believe) is their any article explaining the steps in sql sp2
some people seem to have trouble in the restore step.....Thanks
I just want to be prepared....can I install cd's without the use of the internet....
View 1 Replies
View Related
May 20, 2008
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
View 4 Replies
View Related
Jul 16, 2007
I am new here and new to SQL Express. I've searched for my issue, but can 't quite find anything close to the problem or how to solve it, if it's even solvable. I am using SQL Express on a pc to connect to the back end of a database. The front end application (an access runtime) also runs on the same pc. This pc is on a domain. I think I've tried every combination of protocols, and although connectivity via ODBC is successful, the application can't connect - gives the "server doesn't exist or access denied". When I log on to this computer with the "machine" logon (not the domain), I have SQL Express configured to use shared memory, the application runs just fine. I need to use this database for testing in a non productivity environment, but I really hate to log off the domain to run it. Ideas?
Thanks,
pvdcats
View 3 Replies
View Related
Apr 23, 2008
I have a SQL 2005 SP2 fail-over cluster with several named instances. It has been running for sometime with no problems. Recently I had to add a new windows group to a named instance. From Management Studio I brought up the "Login - new " gui hit search to find the group in out doamin and got the following error mesage:
The program cannot open the required dialog box because it cannot determine whether the computer named "<instance_name>" is joined to a domain.
The id I am using is a domain authenticated account and I used this same proceedure to add windows groups in the past. If I ignore the message and type in the group as domaingroup_name I can still add the group and users of that group can log into the instance. The other named instances have the same probelm. There are no errors in the event log or SQL's log. Any ideas on how to get the domain search back would be appriciated.
View 4 Replies
View Related
May 29, 2006
Hi
How can i make backup on the other computer.
1. SQL computer is express edition, and computers are in workgroup?
2. SQL computer is express edition and is not on domain, and backup computer is on domain.
thanks
alex
View 4 Replies
View Related
Oct 13, 2006
Hi All,
I created SP and enabled db_datareader and db_datawriter for roles for BUILTINUsers on database level.
I can call that SP from my application if I am accessing DB from my app running on the same machine. If I run app on other machine SP throw exception related to lack of permission.
I fixed that by enabling db_owner roles for BUILTINUsers. But it is not good fix from security point of view.
My question is: is there any other way to allow regular user to run that SP by not giving him db_owner privileges?
Also I have to mention that my SP procedure has some dynamic SQL code.
I would greatly appreciate any help,
Thanks,
Roman
View 1 Replies
View Related
Aug 24, 2006
I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.
Any ideas? Thanks!
View 1 Replies
View Related
May 4, 2005
Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?
View 3 Replies
View Related
Jun 21, 2006
Please bare with me as I do not have much knowledge in this area with MSSQL.
I have installed MSSQL 2000 Developer and I have setup a database which I am able to connect to and work fine with from the actual server MSSQL is installed on.
The problem comes in when i try to connect to that same database from a different remote server. It will not connect to the DB from this remote server but I know all the connection information is correct so its something on the MSSQL DB server causing this and not on this remote server.
Someone I know has had this problem and has fixed it but they wont tell me how. All I know is they said something about manually opening the ports on the MSSQL server and he also said when you set it to dynamicly determine the port that MSSQL is listening on it errored out. however if you set it staticly to 1433 it worked fine.
Ive also installed SP4 in attempt to fix this but no luck with that either. And I can also get the same exact remote server to connect to completely different DB server without issues.
If anyone can please help walk me through this I would be greatly appreciate and I am also willing to pay some money if it will make my life easier.
View 6 Replies
View Related
Jan 21, 2004
I am about designing an access project file that connect to a SQL Server database : it could be either on the local computer or a remote workstation, this depending on where the front end is running.
Bypassing the default log on dialog box i have set my own form that users should use to connect on adp. Then using baseconnection string i connect the adp to sql server database.
My concern is how can i check from vba if the instance of SQL Server where my back end database is located is running in order to make
users knowing that due sql server service status off their connection
tentative will fail.
inform u i can check same from console management however i would to automate same controle from my adp and avoid that users have to operate such task...
View 4 Replies
View Related
Feb 23, 2007
we have sql 2005 standard edition. i was trying ( unsuccessfully ) to configure RAS on server 2003 to allow the server to authentificate vpn requests. i don't know how but the next day people started complaining about their mapped network drives being disconnected, and now no one can connect to the database via the network, eventhough they still have access to the server, and the network drives were remapped.
View 2 Replies
View Related
Sep 18, 2007
Greetings friends,
Not sure if this is the right place for my question but here goes anyway.
I have an instance of SQL Server 2005 installed on my DEV PC. A colleague of mine wants to access my server from his machine which connected to the same network.
I logged on to SSMS and added him as a new login.
Will he be able to connect to it now or do I have to do more than just that?
Your help would be appreciated.
View 6 Replies
View Related
Dec 3, 2007
Hello,
is there any way to execute a SSIS package at the SQL Server programatically from a remote computer? (I use .net 2)
I don't need variables or return values, I only want to start the package. I know that it is easier to do that job with a webservice etc on the SQL Server but I cannot use such.
A method to call a package from a stored procedure would also help me a lot
Kind regards, _Rodney_
View 1 Replies
View Related
Apr 7, 2008
Hello,
i'v installed a pogram that uses SQLEXpress and reporting sevices on a clients computer. I can acces the reports wih http://localhost/reports$SQLEXpress from that computer, but i can't reach them from other computers in the netwok by typing http://servername/reports$SQLEXpress
i am asked to imput a pasword for user "servername/guest" (which i do not know and dont think there is one) and then get the following:
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials you supplied.
Please try the following:
Click the Refresh button to try again with different credentials.
If you believe you should be able to view this directory or page, please contact the Web site administrator by using the e-mail address or phone number listed on the preactor home page.
HTTP 401.3 - Access denied by ACL on resource
Internet Information Services
What should i do to solve this problem?
Regards
Darius
View 6 Replies
View Related
Jul 12, 2006
I have 2 xp machines one has the SQL EXPRESS Server with all the goodies on it and the other I just install the client stuff. I am using VB 6 to program with using the ADO. It works great on the server machine. The software pulls up great. I can't seem to get it to work on the client machine. I get "Login failed for user 'TIGGERLGuest'. I found the places to set the TCPIP setings on the server SQL Express to allow this even turned on the browser but nothing same error. I am using the same login on both machines and am pointed to the same workgroup. I used window authentication when I set them up. Not sure why it insists on using Guest.
This is a copy of the connection string I am using
"Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName="c:mpsdbmpswarehouse.mdf;Database=MPSDB;Data Source=tiggerlsqlexpress;Trusted_Connection=yes;"
Not sure if this is okay for connecting remote. Any help would be greatly appreciated
Rick
View 3 Replies
View Related
Feb 15, 2007
I am unable to connect to a remote server on Computer A from Computer B, but when I reverse it I am able to connect to a remote server on computer B from computer A. I have SQL Server 2005 Developer Edition on both computers. I am using the using the SQL Management Studio to connect. Both Computers have these settings
Using Surface Area Configuration both have local and remote connections checked. Both computers using tcp/ip only checked.
Using Configuration Manager both the sql server and the sql browser are running. Both computers have tcp/ip protocols enabled.
Under the server properties on both computers, under the Security Tab, I have SQL Server and Windows Authentication Mode checked. Under the Connections tab I have Allow remote connections on this server checked.
I believe I have the Security for both the server and databases done properly giving them both proper Logins and User Roles with the proper permission.
Also went to my window firewall settings and under the Exceptions tab, I added both the sql server.exe and sql browser.exe programs on both computers on my remote network.
But when I connect on Computer B, I get an error message stating:
TITLE: Connect to Server
------------------------------
Cannot connect to 'ServerName'
------------------------------
ADDITIONAL INFORMATION:
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: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
BUTTONS:
OK
----------------------------
I tried pinging server a and then server b and they both pinged.
Could someone please tell me what could be wrong on my remote computers.
View 6 Replies
View Related
Jun 5, 2007
hi every one. i am a new user of asp.net 2.0 using C# code and i am facing a problem in accessing a SQL Server2005 database file in the remote computer. i have connected two pc with peer to peer connection and trying to add a databse using the "Add connection" option from the visual studio 2005. in the add connection dialog box it is showing me the remote server and it was supposed to show all the database in that SQL Server when i select one. but when i am choosing the server name it was not showing me anything. by the way i have configuered the surface area for "both TCP/IP and named pipes" and both the pc's server browser is turned on. is it the right way to access a database file from a remote pc or not?? please send me a good solution to do this things and try to explain the codes with example. waiting for response....plz send me the solution.. as soon as possible
View 2 Replies
View Related
Feb 6, 2004
Hi all
I am about updating some fields with local date and time using blw API
Quote
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Public Function LocalDateTime()
Dim MyTime As SYSTEMTIME
GetLocalTime MyTime
Debug.Print "The Local Date is:" & MyTime.wMonth & "-" & MyTime.wDay & "-" & MyTime.wYear
Debug.Print "The Local Time is:" & MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond
End Function
Unquote
This works fine however due to it's not really easy for an administrator to keep checking every times wheter local date and time are correctly updated for each front end users computer i would that to reconsider my function and making it to read local date and time from remote SQL Server. my concern is to help administrator to save time as he should only need to ensure that Server is running with correct time...
thkx for any advise
View 1 Replies
View Related
May 28, 2006
Hello Everyone,
I just got a Virtual Dedicated Server and installed SQL Server Express 2005 on it and while I was installing choose Mix Mode authentication and setup a sa password.....
I want to connect to that database remotely from my machine using VS 2005 or Management Studio....both are encountering the same problems.....
On the Virtual Dedicated machine if I install management studio I run has no problem....it asks me for the server name : somethingSQLEXPRESS then username and password and works fine...
The same thing I try to do remotely is not happy with ....I tried to give the IP address of the server same thing no luck.....
Can somebody tell me what I'm doing wrong and where....
Thanks,
Harsimrat
View 1 Replies
View Related
Sep 21, 2007
Hello,
When I try to access a SQL Server report on the localhost that I have deployed, I get a message that "under default settings SQL Server does not allow remote connections." After doing a little bit of searching, I went to the SQL Server Surface Area Connection and changed the server instance to allow for local and remote connections of both TCP/IP and named pipes. I stopped and restarted the service. Even after doing this I still get the same error.
If the Surface Area Configuration can't help out in this situation, what else would need to be modified?
Thanks,
CCG
View 2 Replies
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 7 Replies
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 2 Replies
View Related
May 1, 2015
I have a remote server where I dont have RDP access.
I opened Powershell and wrote the below query to read some of the message, but getting error:-
Clear-Host
$Machine = "XXXXX"
#Get-Eventlog -Logname System -ComputerName $Machine -newest 1000 | Where-Object {$_.EventID -lt '100'} |
Error Message:
[XXXXX] Connecting to remote server XXXXX failed with the following error message : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet.
+ CategoryInfo : OpenError: (XXXXX:String) [], PSRemotingTransportException
+ FullyQualifiedErrorId : WinRMOperationTimeout,PSSessionStateBroken
How to read the data (app/system eventviewerlog) using xp_cmdshell from SQL Server Mgmt studio itself?
View 2 Replies
View Related
Jan 5, 2007
Hi,
I have Windows XP Home Workgroup LAN.
I am treating one of the XP Home system as 'Server' and installed SQL Express 2005 SP1. Enabled remote connections access.
I have total 4 XP Home systems, out of which one i am treating as Server, from the rest 3 computers, 2 are successfully connected to the Server and able to access SQL Express without any problems. But the 3rd system is unable to communicate with the Server. I disabled all firewalls in this 3rd system but still unable to connect to the server. I am able to ping the server, browse for shared folders on the server. Except SQL Express remote connection, everything else in the network is accessible from this 3rd computer.
Can anyone help me on how to troubleshoot this 3rd computer sql express connection problem to the server.
I don't have any error message to provide here. It just says, unable to connect to sql server, verify username/password etc.
Is there any limitation on SQL Express remote connections ?
View 1 Replies
View Related
Jun 22, 2007
All of the books I have about Visual Basic 2005 Database Programming, gives examples of connecting to a database on a localhost computer. But what about the connection to a database on a sql server 2005 on a computer wich can only be reached via the internet ? Is that possible without using ASP.NET ?
My problem:
I have a desktop with a sqlserver 2005 database . This desktop is connected with the internet . The IP address is a dynamic IP address.
On my laptop is a ADO.NET 2.0 program that needs to be connected with the database on my desktop.
What kind of address do I have to specify in my connectionstring in order to get connected to the database on my desktop , which can only be reached via the internet ?
I'm I wright to suppose that in the case of a static IP address, all I have to do is put the IP address in the connectionstring in order to get connected with my desktop via the internet? Are there some changings to be made in the settings of the SQL SERVER 2005 on my desktop ? What about the security ?
On the other hand , because the address of my desktop is a dynamic IP address, how do I solve this problem.?
I've heard about a program DynDns that can solve the problem of the dynamic address. Can i use this program together with my ADO.NET program to solve the problem , and how do I specify in this case the address to the database on my desktop in the connectionstring?
Is there someone who has experience with that program and the problem I described here ?
Thanks for the help !!!
View 1 Replies
View Related
Jan 19, 2007
Hello
We have a setup of two very separate domains, they are connected via a couple of routers, we connect to the remote domain mainly for administration mostly via remote desktop.
We setup the same user name & password on both domains, with XP as the operating system we can setup the sql link via odbc using windows authentication which works fine, a couple of PC's have now been upgraded to Vista but the increased security stops this from working, we now get error 18452 error code with the user name of "null" I assume vista has stopped the passing of local credentials to what it considers an untrusted domain, fair enough, does anyone know a way of either adding another trusted domain to Vista ? I do not want to setup an AD trust between the two.
Thanks for any help
John
View 9 Replies
View Related
