If using sql server authentication between two SQL Server instances (firewalled and on different networks),how are the connection details transported, specifically, is the IP addess, username and password sent in clear or is their some internal obsfuscation/de-obsfuscation mechanism native to SQL Server in operation under the covers?
View 1 RepliesHi,
In a stored procedure I have, I need to make a call out to a DR server, I believe the named pipes protocol is causing errors for long running sql. Is there anyway I can change the call
exec svr_dr.msdb.go
to ensure that it uses the tcp protocol rather than named pipes?
Thanks,
JS
Hi everyone,
I want to use TCP/IP network protocol on the server and have my clients using TCP/IP Net library. I have having some problems with this...The server will not register if I only have TCP/IP without Named Pipes using Standard Security in SQL Server. If I have TCP/IP and Named Pipes, the client can't register the server. Can someone explain in detail to me on using TCPIP on the server and client?
Thanks...
Has anyone ever seen TCP/IP become a disabled protocol without manual intervention from a user?
My websites just stopped talking to my MSSQL db and when I went into sql client config utility on the web boxes, tcp/ip was disabled. Any clues? Can't find anything to support this in the event or web logs.
I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?
Thanks!
Hi everybody,
I dont know if this is the right place to put in this question.
The problem is
I have an application developed using VB 6.0 and SQl Server 2000. It was working fine on my machine. I had MS XP installed on my system alongwith SP1. But when i installed MS XP SP2, i got the error while saving the record.
the error is
"Protocol Error in TDS Stream".
I went to microsoft site searcing for the resolution of this error. They gave the solution of installing MDAC 2.8 SP1. But when i install MDAC 2.8, it gives me this error
"MDAC 2.8 RTM is incompatible with this version of Windows. All of its features are currently part of Windows."
And since MDAC 2.8 is not installed so i can not install MDAC 2.8 SP1 also.
I have MDAC 2.5 installed on the system.
How can i resolve it. Please give a solution as soon as possible. I am in great need of it.
I am using SQL Server 6.5. By default TCP/IP protocol is not enabled (1433 port is not opened by the server). How can I enable this so that I can use a JDBC driver to connect to it.
In SQL Server 2000, the "SQL Server Network Utility" helps us to enable the TCP/IP protocol. But not able to figure our how/where this can be done in 6.5.
Thanks for any help
Can anyone point to a reference which documents the pros and cons of the various connection protocols, such as Shared Memory vs. TCP/IP? I thought I saw something indicating that shared memory is fastest, which would explain why this protocol is tried first, but now I can't find it. This resource has information on creating connection strings, but not the advantages and disadvantages.
http://msdn2.microsoft.com/en-us/library/ms187662.aspx
hi all,
Happy new year!
is anybody here familiar with the different informix ODBC protocol
such as Sesoctcp and onsoctcp and the rest of the protocols
I think my SSIS design is affected by this protocols i'm having problems with
the unicode and non unicode conversion.
many many thanks
Joey
Hello,
I cannot connect to a SQL Server that is in domain "x" from my client which is in domain "y" using
Multi-Protocol. There is no trust between domains x and y. The SQL Server has Multi-Protocol installed and is working
correctly ie. I can connect to it from other clients in the same domin via Multi-Protocol.
I have tried multiple things to make this work. Adding the SQL Server to my LMHOST file. Adding advanced settings for the
SQL Server in my "SQL Client Configuration Utility".......All with no avail. I have received different error messages which I have
found little about. "ConnectionOpen (RPC open())", "OS ERROR 1703", "OS ERROR 11".......
Has anyone had similar problems? If so, is there a fix or something that I could be missing?
Thanks in Advance!!
Tim
Hello ,My question is:does ms sql support two phase commit protocol?and Why if no.How if yes.Thank you
View 2 Replies View RelatedOur client is trying to access the report builder from there machine via a proxy and it does not work, What would be all the ports and protocol (http,https,ftp) needed to be open for the report builder application to work normally behind a proxy? or does anyone know what are the ports and protocol used by report builder ? because we need to provide this information to our client so they can access our report server.
Chi.
I have a 2 node SQL Server 2005 cluster with 2 named instances. Both appear
to be working properly with no issues but I have the following warning
message in the event viewer and I would like to know if it is something I
should be worried about.
Source: SQL Browser
Type: Warning
Category: None
User: N/A
Description: The configuration of the AdminConnectionTCP protocol in the
SQL instance XX is not valid.
The servers in the cluster are running with Windows Server 2003 X64 Enterprise
Edition, SQL 2005 Enterprise Edition SP2 X64.
I have failed the cluster over and I continue to get the message when I
start the instances in the cluster admin, but I don't appear to have any
other symptons. Client machines can connect without any problem and
transactional replication works no matter what node I bring the instance up
on.
I changed default TCP/IP port and disable/enable Named pipes, but no luck so far.
I see lots of postings about this message but it seems to be related to
SQLExpress and I am not using that here.
Any info would be appreciated.
Scenario: Private SQL server that has a v6 public facing address. How can I disable the interface over my public address only but keep functionality over the local address?
IP1: 192.0.0.1
Active: Yes
IP2: 2600::5
Active: Yes
IP3: fc2e::5
Active: Yes
When I try to change IP1 and IP2 to Active: No it tells me file is read only.
I am loading data from one server to another server using SSIS,After loading some rows i am getting the below mentioned Error.Each time i run i am getting the error in different Dataflow tasks in the package.
Error :
"
[OLE DB Source [199]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on OLE DB Source returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.There may be error messages posted before this with more information about the failure.".IS this SSIS package Error or Network related?
Can anyone help with this problem?
I have a green light on my Report Services Confuration, and can configure the server within sharepoint integrated mode. I have also assign the appropriate permission for aspnet etc..etc.
From within Business Intelligent studio when I tried to publish my reports I get the below error:
The server commited a protocol violation
Here is a copy of the report server services log file.
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.3042.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Daylight Time</TimeZone>
<Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerService__main_06_21_2007_10_47_38.log</Path>
<SystemName>DMTXDW01VM2</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
<OSVersion>5.2.3790.65536</OSVersion>
</Header>
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing ConnectionType to '1' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
ReportingServicesService!servicecontroller!9!21/06/2007-10:47:46:: i INFO: Total Physical memory: 1073094656
ReportingServicesService!servicecontroller!4!21/06/2007-10:47:47:: i INFO: RPC Server started. Endpoint name ='ReportingServices$MSSQL.3'
Urgent help required!
Hello,
I am getting the following warning in the Application Event Log:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
The warning is logged by the SQLBrowser service.
This is happening on Windows 2003 server with SQL Server Express 2005. On the same machine SQL Server 2000 is installed.
I am currently unable to connect from remote clients to the instance of SQL Express and I believe it has to do with this warning (I've tried all the usual remote connection troubleshooting without success - enabled protocols, SQL Browser Service running, there is no firewall on the server, I disabled firewall on the client when trying to connect).
Does anyone know how to resolve this issue?
If you need additional information please let me know.
I am accessing a SQL2005 Express SP1 Server via TCP/IP (all machines fully up to date with windows update). For ease of configuration the Server IP is mapped to an Alias in Native Client Configuration Manager. On the Client Force Protocol Encryption is set to Yes and Trust Server Certificate is set to No. The clients are accessing the DB via SQL 2005 Management studio express and Ms Access 2003 (an access adp front end to the sql database).
As a test i turned off force encryption on the server. A Network trace shows no encryption! Turning on force encryption on the server corrects this.
As a further test I configured the server with a certificate the client doesn't trust. No error is generated by the un-trusted certificate! The only way to generate the certificate error is to enable encryption in the connection properties of each application. Why isn't the Native Client Configuration Manager utility enforcing this???
This brings me on to a second (off-topic problem). In my access adp file, if I enable €œuse encryption for data€? advanced option under the database connection dialog all works as expected (encryption + certificate verification). However, I am programmatically updating the connection in VBA
Application.CurrentProject.OpenConnection "Provider=SQLOLEDB.1;Data Source=AliasName;Use Encryption for Data=True;..."
If I do this no encryption is enabled!!! Access seems to just ignore the encryption statement.
Has anybody got any ideas as to why either of these issues are occurring or if there are any workarounds?
Thanks
Stuart
I've been reading about the protocols and i've been trying to connect to my SQL SERVER using each one of the protocols, but i think I'm missing something, i dont have any problems using Shared Memory and TCP protocols, but I'm not pretty sure how to connect to my remote SQL SERVER using NAMED PIPES protocols. The books On Line of Microsoft refer that you can connect using this protocol using the next syntax: Connecting to a default instance by specifying a named pipe name:
APPHOSTpipeunitapp
Connecting to a named instance by specifying a named pipe name:
APPHOSTpipeMSSQL$SQLEXPRESSSQLquery
Connecting to default instance by name, forcing a named pipes connection:
np:APPHOST
Connecting to named instance by name, forcing a named pipes connection:
np:APPHOSTSQLEXPRESS
But I can't connect to my remote SQL SERVER, if i try the same with my local SQL SERVER i don't have any problems. I've been reading the articles so many times searching something about Named Pipes is only for local connections, but Microsoft say that it's posible to connect to a remote SQL SERVER, but i don't know how to do that. The firewall is disabled, I changed the APPHOST in the above syntax for the IP address of my SQL SERVER but it didn't work..
What is the best way to transport a complete database (including tables and stored procedures) to another PC?
View 2 Replies View Related
I am using the TRANSPORT and self-routing and it works great. My question is: is there ever a need for more than one transport route when using multiple services, or should there just be one per database, generally speaking? What exactly does the presence of this route tell service broker? It seems as though it just signals service broker to use self-routing if it can't resolve the service any other way....similar to the way the LOCAL route works, only for remote services.
Ok, some background information:
I'm running an asp.net application.ASP.NET 2.0I'm using a SQL Server 2000 database.I don't have very many problems with the application, but when I do, I get this error message:
Inner Source
.Net SqlClient Data Provider
Inner Message
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Here is what my connection string looks like, although from the stacktrace, it looks like its failing when I do an ExecuteReader. Any ideas why I'm getting this error?
connection_string = "Data Source=database;Initial Catalog=catalog;User ID=XXXX;Password=XXXX;min pool size=1; max pool size=50";
Thanks,
Khanathor
Hi:
I exec local procs of archiving databases on 3 SQL 2000 Enterprise Servers. All three servers with following same error messages in the middle of processing(each had processed from 10 to 100 DBs already).
----------------------------------------------------------
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
There is no error message in above 3 SQL's error log and all of them are up running fine.
Since the procs are in local, I did not use hostname or '.' to refer as 'Local'.
Any idea?
thanks
-D
Hi,
I have data like this:
Appid | CID | Level | Col1 | Col2 | Col3
1 | 100 | man
1 | 101 | null
1 | 102 | null
2 | 100 | null
2 | 101 | xyz
2 | 102 | null
3 | 100 | null
3 | 101 | null
3 | 102 | abc
I have only 3 values for the CID column. 100, 101, 102.
I need to get result like this:
AppID | L1 | L2 | L3 | Col1 | Col2 | Col4
1 man null null
2 null xyz null
3 null null abc
How to get this result? The Level column values are not in alphabetical order. Here L1 column contain values of CID is 100. L2 column contain values of CID 101. L2 column contain values of CID 102
Thanks in advance
Hi There
SInce day one i have configuring service broker to use transport and dialog security with certificates.
However i am not 100% sure how to setup service broker between 2 instances without security.
Would the following work, or what steps am i missing?
1.Setup the endpoint with WINDOWS authentication instaed fo certificates, the instances are in the same domain, grant necessary permission to the service account for each instance.
2.Create dialogs with the create remote service bindings with the anonymous = ON and grant send to public for the services.
Would this work? Also am i correct in thinking that if encryption is required the use of certificates is mandatory?
Thank YOu
Is there a configuration parameter that allows me to control the number of Transport connections used by service broker? Right now it seems that it just uses one.
View 3 Replies View RelatedI am getting the following error.A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - The system cannotopen the file.)On performing the same operation again, it happens fine without anyerror.Please help.Regards,Shilpa
View 5 Replies View RelatedI saw a posting or read a blog somewhere that mentioned custom WCF transport channels where created for interacting with ServiceBroker. Has anyone read or heard something similar? If so, where can I get them?
Thanks.
Phil
If all my SQL Server instances are mutually trusted, am planning to implement transport layer security with Windows authentication. My query is that if I'm using Windows authentication do I need certificates to be created? Though I am using Transport security, I can achieve encryption by ENCRYPTION - ON in the Begin Dialogue conversation. I assume Message integrity using MD5 signatures are by default provided by Service broker irrespective of whichever service options we choose?
Can I have some article references on how these security mechanisms will impact the performace of Service broker communications?
Thanks a lot,
I have an SSIS package that calls a stored procedure via an ADO.NET Execute SQL Task. This stored procedure is long running - up to an hour. I am running the SSIS package on the same SQL Server that the SQL Task is connecting to.
I started to receive an error last night when calling it on my DEV box: "The semaphore timeout period has expired." I rebooted the server and tried again. Same error. I added some additional logging to see where I am within my stored procedure when the error happens and rebooted again. Same error. I made some other minor changes and rebooted and tried again. This time the error changed to: "The specified network name is no longer available".
This is running on a SQL Server 2K3 box. With the latest patches, including 4 that were installed this week.
Am I correct in interpretting these errors to say that the SSIS component is unable to maintain the SQL connection (to the same box) for the duration of the stored procedure run? Any other questions/advice?
Here are the 2 complete error messages:
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks,
Chris
Hi
I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.
After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing
alter database set partner="..."
Am I missing something?
Thanks
How do I take a database developed and operating and get into the 2005 SQL Server?
View 4 Replies View RelatedHi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-------------------------------------------------------------------------------------------
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
------------------------------------------------------------------------------------------
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
------------------------------------------------------------------------------------------
-Then if I from a query, first creates the user, then drops the user, I can't create him again?? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik