I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
I have a SQL Server 2005 instance which resides on a dedicated server hosted by a third party. I am able to connect to this server through my local SQL Management Studio, however, my local ASP.NET 2.0 web application cannot connect. I get the error below. The connection string I use is formatted as... Connection String: Server=thesever;uid=myuserid;pwd=mypassword;database=mydatabase Error: Failed to connect to database: 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) The hosting providr claims that the SQL Server configuration settings are as they should be as fars as TCP/IP and Named Pipes are concerned. Does anyone know what might be going on?
I'm running Windows Xp Pro With SQL 2005 Dev edition with Adventure Works sample db installed. I am the local administrator on my desktop, the services are up and running and I specified local connections only since this is the DEV edition of SQL05.
When I try either one of these connection strings: Data source=(SQLDEV05); initial catalog=AdventureWorks or Data source=(local); initial catalog=AdventureWorks.
I receive this error message:
TITLE: Microsoft Report Designer -----------------------------
A connection cannot be made to the database. Set and test the connection string.
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: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:
I am facing a problem in connecting to the local database with server name as (local).
I have installed SQL Server 2005 in my machine. When I try to connect to the SQL server with the server name as SUNILKUMAR I am able to connect but when I try to connect to the same server with the server name as (local) I am not able to connect. SUNILKUMAR is my machine name and SQL server is running locally.
if anyone can help me what is the problem in this case it is highly appriciated.
I have an application I developled for a shopping cart function using Wrox ASP.NET book as a guide. I have everything working great on my local system using SQL 2005 Express. When I move the app to an IIS 6.0 Web Server with SQL 2005 Express installed I get the following error. Line 103: Public Overridable Property Cart() As Wrox.Commerce.ShoppingCartLine 104: GetLine 105: Return CType(Me.GetPropertyValue("Cart"),Wrox.Commerce.ShoppingCart)Line 106: End GetLine 107: Set It has some comments up top about not being able to establish a connection to the SQL Express Database. I have made the changes to allow TCP/IP and Named-Pipes The App has to be connecting to the database because it displays grid views during the order process. The Database is located in the App Data folder IIS is set to Integrated Authentication and configured as a Web Application. Can someone please tell me what is wrong. thanks.....
I have a application which uses the automatic asp.net membership stuff to make it work. (In app_data). It works fine on local host, but when I use it on the web server I get the error message (after clicking the log in button):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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Please help me fix this. Thank you for your help,Sam
Hi. I'm (very) new to SQL Server. I have run into what seems to be a very common problem, judging by the number of Google hits, but it's not actually in the FAQs here, as far as I can see.
Situation I installed SQL Server Express with Advanced Services 2005 on my Windows XP Pro laptop, using (with one exception) the default install. The exception was to put the data files on a separate partition (D: ) where I have more free space.
SQL Server Management Studio Express connects happily, and I set up a single data import table.
Issue: bcp Fails to connect to default local instance I initially tried bcp in output mode, prior to working in earnest on importing my data. However the command line-
bcp MyDatabaseName.dbo.UserProfile out UserProfile.txt -T -c
Fails with the error-
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
As I understand it, the local instance of SSE is the default, and should use a local (shared memory?) connection. This seems to work for Management Studion Express, but not bcp.
While investigating this problem, I tried enabling remote connections (all types) through the Surface Area Configuration wizard, and found that remote connections using either of:
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S localhostsqlexpress bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S mycomputernamesqlexpress
both work ok.
Extra Info When starting Management Studio Express, it says something like "Configuring environment for the first time". However it says this every time it is started, not just the first run after installation, does this indicate some configuration data is not being successfully stored?
I have found threads that look similar to my issue-
However these do not relate directly to bcp, and to be honest my knowledge of SSE is slender enough at this stage that i'm unsure if there are really relevant.
I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98 There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
Hi all,I'm having a big problem here. I can't seem to connect to a database of MSSQL Server 2005... Here is what I've did:1. Under the Server Explorer>Data Connections. Right click and choose "Add Connection..."2. Data Source was "Microsoft SQL Server Database File". Click OK.3. I browse to the database in the MSSQL.2/MSSQL/Data folder.4. Click on the "Advanced" button, and change the default DataSource from .SQLEXPRESS to .MSSQLSERVER. Click OK5. Received this error: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 setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) Only .SQEXPRESS that I was able to connect to... i've tried the solution from Microsoft, opened SQL Server 2005 Surface Area Configuration and enabled "Local and remote connections", checked the option "Using both TCP/IP and named pipes" but it didn't help. This is frustrating because I can't get the Membership provider, login to work on my host. Please help. Thank you very much,Kenny.
I know this is strickly not a website question, but dunno where else to post...To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting serverI have the following code: Dim StrSQLUN As String = "[UN]" Dim StrSQLPW As String = "[PW]" Dim StrServer As String = "[IP][INSTANCE]" Dim StrDB As String = "[DB]" Dim strTimeOut As String = "Connection Timeout=0;" Dim pStrSQLConn As String = "Server=" & StrServer & ";Database=" & StrDB & ";User Id=" & StrSQLUN & ";Password=" & StrSQLPW & ";" & strTimeOut
Dim sqlConn As New SqlClient.SqlConnection(pStrSQLConn) If sqlConn.State = ConnectionState.Closed Then sqlConn.Open() This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct? Can they put restrictions in place for this specific sort of data access?Does anyone have any suggestions on how to resolve this issue??Thanks for any helpAdam.
Hi all, I have moved hosting companies and am trying to get my website and MSSQL database sorted on the new server. I am using an enterprise type tool and can connect to the database the new hosting company has created on their SQL server with no problem. I now want to upload the data I had in the database previously. All I have to do this from is .BAK and .TRN files on my local hard drive. Using the restore option from the menu, I tried to select the .BAK file I have saved to restore from it, and got the following error:
"EXECUTE permission denied on object 'xp_availablemedia', database 'master', owner 'dbo'."
I also tried the advice given in this post as the problem seems similar and follows the same pattern: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841
but there are loads of users listed for sp_who - it seems to be the users for all the databases on the host's server so I didn't follow the next step to disconnect them!
Any advice as to what I need to do would be much appreciated.
Hello-I have a Sql Server 2000 database offsite that I would like to back upto a local machine. I am using Enterprise Manager on a local machine toadminister the remote db.Whats the best way to schedule backups so the remote db is backed up tothe local machine?Can this be done in Enterprise Manager?Would you recommend any 3rd party software?Thanks!MB
What is the usual way to develop ASP.NET websites locally using a database and then migrate everything to the real webserver? Here's what I'm doing now -- tell me what I should be doing instead (I'm a newbie developer).
I'm using Visual Studio 2005 Standard with a SQL Server 2005 database, to develop a small C# ASP.NET website. I'm the only developer.
I have a page that connects to SQL server. It works fine when running in local host but when I publish to the server, I get the following: 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) Any ideas on where to start? Thanks,Mark
Hello, I need help to connect to my local sql express db in visual studio 2003 in vb.It currently works with the production db information and I need to test a few things, So i'd like to point it to the local db.It works this way:Const CONN_STRING = "server=johndoe;user id=john;password=doe;database=johnd"Dim conn As SqlConnection = New SqlConnection(Me.CONN_STRING)conn.Open()I want to point it to my local db and it doesn't connect. Dim connectionString As String = "Data Source={myusernameSQLEXPRESS};Initial Catalog=johnd;Trusted_Connection=yes" Dim conn As SqlConnection = New SqlConnection(connectionString) Try conn.Open() Catch ex As Exception Response.Write(ex.ToString()) End Tryand even that doesn't work.When i go to my sql server 2005, it saysMYUSERNAMESQLEXPRESS (SQL SERVER 9.0.3042 - theworknetworkmyusernamewhat am i doing wrong ?Thanks.
i have problems connecting to the local server on my laptop. i ahve used the following code but it gives me an error saying login flaied for user "sa" or "sekhar".
i tried using the username and password i use to log onto my computer. but it doesnt work. can anyone tell me how to do it?
the code is typed is
connection = new sqlconnection("server=localhost;uid=sekhar;pwd=sekhar;database=chandu") ........................ ...................................... ............................... connection.open()..................i have all the next statements correct. my username is sekhar and the password is sekhar too but it gives me an error. somebody help me
I am trying to set up a new sql server registration to my local computer. and there's this error - "General Network Error,Check your network documentation.ConnectionOpen(Connect()). I have already tried to register the server(the available servers field) are entered as my computer name,local IP address,(local),"."(ignore the double quote), and even 127.0.0.1,but the same error occured. When i first installed it into my computer, it work for a while, but ultimately MSSQLServer and SQLServerAgent service stopped even though the MSDTC service is still running For ur info, the installed software is MSSQL7 7.0 Desktop Edition, in Windows 98 Platform
Hi !! everbody i have a problem in sql executive ( 6.5 version),whenever i am using cmd.exe for running scheduled task it runs fine but if i am using TSQL task type to run a task then it gives error and says that 'unable to connect to server '(local)'. our this TSQL task type was running previously but now for last 5 days itis not runnig.
Your reply would be highly appreciated Pl. reply as early as possible
I had Sql Server 2005 installed on a machine of mine and was able to connect to the local, however I had to unistall it. Recently I put it back on the machine and found that I was no longer able to connect to the local server.
Does anyone know why this has happened and how it can be resolved? Thanks
During my install of SQL Server 2005 Developers edition, I got an error that Reporting Services could not connect to SQL Server. After completing the installation I was unable to connect to the local service using an domain account (that's an admin on my machine) or the SA account. I've retried uninstalling and reinstalling numereous times and no success. I was able to login to the IP addess with the SA account only.
I did set the config for remote access...
Shared Memory is Enabled
Named Pipes is Enabled with Pipe Name = \.pipesqlquery
TCP/IP is Enabled, Listen All, IP Address active and enabled for 127.0.0.1 and network address, TCP Dynamic ports not set
When I login with an IP address and an local admin account I get this error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
When I try to login to the machine name with a local admin account I get this message:
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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)
When I try to login to the machine name with the SA account I get this message:
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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)
I can login with the SA account using the IP address. I can login with a local admin account for 127.0.0.1
Any ideas what's going on? Why doesn't uninstalling and reinstallingh fix this? Is there a tool that will purge the registry of all remnants after an uninstall? Any help would be appreciated.
I can't connect to the local sql express server. I see in microsoft sql server management studio express that the server is running but I don't fint the server in access 2003. I work with vista. what can be the reason?
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Hi I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible? If i just get the connection part explained, i can figure out the rest by myself. Thanks /Jonas
Hi and thanks in advance for your help. I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
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...
