hi guys,
do you know if we can use sql authentication to connect to SSIS through SSMS. right now it's only windows authentication, and SQL authentication is grayed out.
Not sure if this question has already been answered because it sounds like easy question.
I have installed a SQL Server 2005 Standard Edition instance with SSIS on Window Server 2003 x64. From my PC, I am able to connect to the Integration Services on the server using Windows Authentication but not with a local SQL login - and the option to change authentication methods is greyed out.
Is it possible to remotely connect to SSIS using a SQL login rather than a Windows login? If so, any ideas or references for configuring it as such?
Thanks.
- Lance
Error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
Â
Here are the steps I followed:
To configure rights for remote users on Windows Server 2003 or Windows XP
Just installed SQL Server 2005 Standard. When I attempt to connect using SSMS it just sits. No error comes up. The log seems to indicate everythings OK. SQLServer and SQLBrowser services are started. Here's the log:
2007-07-07 09:41:23.07 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2007-07-07 09:41:23.07 Server (c) 2005 Microsoft Corporation.
2007-07-07 09:41:23.07 Server All rights reserved.
2007-07-07 09:41:23.07 Server Server process ID is 2412.
2007-07-07 09:41:23.07 Server Authentication mode is WINDOWS-ONLY.
2007-07-07 09:41:23.07 Server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2007-07-07 09:41:23.07 Server This instance of SQL Server last reported using a process ID of 1348 at 7/7/2007 9:35:40 AM (local) 7/7/2007 2:35:40 PM (UTC). This is an informational message only; no user action is required.
2007-07-07 09:41:23.07 Server Registry startup parameters:
2007-07-07 09:41:23.07 Server -d C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2007-07-07 09:41:23.07 Server -e C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2007-07-07 09:41:23.07 Server -l C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2007-07-07 09:41:23.11 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-07 09:41:23.11 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-07-07 09:41:23.34 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-07 09:41:23.36 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-07-07 09:41:23.89 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2007-07-07 09:41:23.89 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-07 09:41:23.90 spid5s Starting up database 'master'.
2007-07-07 09:41:24.20 spid5s SQL Trace ID 1 was started by login "sa".
2007-07-07 09:41:24.28 spid5s Starting up database 'mssqlsystemresource'.
2007-07-07 09:41:24.31 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-07 09:41:25.65 Server A self-generated certificate was successfully loaded for encryption.
2007-07-07 09:41:25.67 Server Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMSSQLSERVER ].
2007-07-07 09:41:25.67 Server Server local connection provider is ready to accept connection on [ \.pipesqlquery ].
2007-07-07 09:41:25.67 spid9s Starting up database 'model'.
2007-07-07 09:41:25.67 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2007-07-07 09:41:25.67 Server Dedicated admin connection support was established for listening locally on port 1434.
2007-07-07 09:41:25.68 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-07 09:41:25.68 spid5s Server name is 'LAPTOP'. This is an informational message only. No user action is required.
2007-07-07 09:41:25.71 spid12s Starting up database 'msdb'.
2007-07-07 09:41:25.71 spid13s Starting up database 'ReportServer'.
2007-07-07 09:41:25.71 spid14s Starting up database 'ReportServerTempDB'.
2007-07-07 09:41:25.71 spid15s Starting up database 'testx'.
2007-07-07 09:41:27.45 spid9s Clearing tempdb database.
2007-07-07 09:41:28.65 spid9s Starting up database 'tempdb'.
2007-07-07 09:41:28.78 spid5s Recovery is complete. This is an informational message only. No user action is required.
2007-07-07 09:41:28.78 spid12s The Service Broker protocol transport is disabled or not configured.
2007-07-07 09:41:28.78 spid12s The Database Mirroring protocol transport is disabled or not configured.
2007-07-07 09:41:29.25 spid12s Service Broker manager has started.
2007-07-07 09:57:23.54 Server CPU time stamp frequency has changed from 117988 to 200203 ticks per millisecond. The new frequency will be used.
2007-07-07 10:09:23.85 Server CPU time stamp frequency has changed from 200203 to 242307 ticks per millisecond. The new frequency will be used.
2007-07-07 10:20:03.20 spid12s Service Broker manager has shut down.
2007-07-07 10:20:04.25 spid5s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2007-07-07 10:20:04.25 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Is there a roadmap for allowing SSMS to connect to a PDW (MS SQL2012 Parallel Data Warehouse) with full functionality? Current support leaves much to be desired without installing third party tools.
View 3 Replies View RelatedI've installed SQL Server 2005 SP1 RTM on a Windows 2003 R2 machine (machine A), and my local workstation (machine B).
From machine B, I can use SSMS to connect to SQL Server on machine A. This is using either "New Query" or "Object Explorer".
However, on machine A, SSMS can only connect to machines A or B using the "New Query" option. If I try "Object Explorer" for either, I get the error message "Failed to retrieve data for this request... urn could not be resolved at level Configuration."
I'm not sure what died (no pun intended) since the last time this worked. Any thoughts on this?
We are running SQL 2005 Standard on Windows 2000 Server Standard. We are trying to connect via SSMS with the Object Explorer to the server and it is failing with an error message stating:
"An exception occurred while executing a Transact-SQL statement or batch.
Additional Information:
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)
Using profiler, I tracked down that it is a call to xp_instance_regread. This is something that SSMS is issuing - not me.
We've found in the past that rebooting the server fixes the problem. But it keeps cropping up. The only thing running on this server is SQL 2005 - so if something is monkeying with the registry - it's got to be SQL Server itself.
Querying works just fine - we can use the editor to perform queries without any issue - it's just explorer that goes kaboom.
We have SP2a installed on the server and on most (if not all) of the clients.
This is getting frustrating because it's a production server and we're having to reboot this to fix it.
Hi,
In a nutshell I'm unable to connect to SSRS within SSMS yet I can connect to SSIS, SSAS and the Database Engine within SSMS from the same named server instance ("BOODEMO")
"SQL Server 2005 Surface Area Configuration" reports that service status SSRS (DEMO) is running.
The only other problem that I have found which may be linked is that within "Reporting Services Configuration Manager BOODEMO" there is a red circled crossed next to "Report Manager Virtual Directory", yet this application has just recently reported that I successfully created a virtual directory.
Any help or constructive guidance would be greatly appreciated.
Thanks in advance,
Kieran.
Every time I start SSMS 2012 the "Connect to Server" box is partially obscured in that the top of it, the move bar can't be accessed. I've manually moved it using "alt+space" then "M" followed by the arrow keys, but it's getting annoying. Â Is there any way to change the initial start position of this box?Â
View 6 Replies View RelatedI am working with SSRS. I do have admin rights. I can connect to the url https://localhost/Reports and can create the new reports and subscription. Delete the reports and subscriptions.
But when I tried to connect to Reporting Services from SSMS, I am getting below error.
I used the windows authentication. It is showing that the user name or password is not valid or you don't have permission to access this server
I can connect to the database engine and Integration services but unable to connect to the Reporting services.
Hi,
I am able to run an SSIS script from BI Visual Studio no problem, I am also able to connect to SSIS within SSMS successfully. I have system access rights to the Database Engine of the same server name.
However I have a user who also has full system access rights to the Database Engine of the same server name. However he can only connect to the database engine, he cannot connect to SSIS within SSMS, but he can successfully execute a SSIS script within BI Visual Studio which is targeted to the same server name which he can€™t connect to within SSMS.
Any help would be greatly appreciated.
Kind Regards,
Kieran.
I recent installed a named instance of SSRS 2005 on a remote server. The server has a side by side install of SSRS 2000 and SSRS 2005, with the SSRS 2005 being a named instance. The server also has SSL and MS Sharepoint Portal Server installed.
When I try and connect to the SSRS 2005 instance with my client install of Mangement Studio, I get the error message:
"The underlying connection was closed: Could not establish a trust relationship for the SSL/TLS secure channel. (System.Web.Services)
Additional Information: The remote certificate is invalid according to the validation procedure. (System)."
Obviously, this is a SLL issue, but, not being a security guru, I have no clue on how to fix this. I do know the certificate is valid, not expired and properly working. Both installs of SSRS are set up using the SSL certificate. Also, I am not connecting to SSIS with Management Studio, as I heard there is a bug connecting to both SSRS and SSIS at the same time.
Anyone have any ideas?
Thanks!!
Jeff
Version: SQL SERVER 2012 enterprise edition and SP2.
Due to one of the security reason, I have changed default port 1433 to another port number in configuration manger tools->protocols for MSSQLSERVER->TCP/IP --> IP ALL section mention another port number.
After restarted service, SQL SSMS able to connect server itself and new port number also LISTENING.
But SQL client SSMS not connected to one of the PC side after changing default port.
I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamics FINANCE) using SQL authentication from local SSMS, I get below error message.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)I assigned a static port number to the named instance [dynamicsFINANCE]Â 1450. I also setup the firewall rule to allow access to Port 1450.
View 4 Replies View RelatedHi,
I have gone through so many kb articles to understanding the problem, but no luck as yet.
Here is the situation:
I have two dev servers : Server A and Server B. Both are set up in an identical fashion. Windows 2003 sp2, Sql Server 2005 sp1 Developer Edition, both are on the same domain, Both have all the three protocols enabled: Shared Memory, Tcpip, and Named Pipes in that order. I can ping both servers, and telnet using the default port. Both have only default instances. Both use the same domain user account for sql service. Both have the error in the log saying that spn could not be registered so authentication may fall back to NTLM.
Here is the issue: When I try to register Server A specifying tcp-ip connection, I am successful and I can see that the authentication is NTLM - is as expected. since the spn registration did not happen and since the sqlservice account is not a domain admin, the authentication used NTLM over tcp-ip. However, I try to register Server B specifying tcpip, I get a cannot generate sspi context error. Why did the authentication not fall back to NTLM as in Server A? From what I read in the KB articles, I asked our network admin to check up the spn, but he assures me that spn were never manually registered.
Can somebody throw more light on this issue?
Thanks
KRSE
I need to install SSIS/SSRS in SSMS. I have connected the SharePoint list view to excel for reporting purpose but due to data size the performances is very slow in excel and now I would like to connect the SharePoint List to SSIS/SSRS
I have windows 8, 64 bit system
I have installed SQL Server Management studio 2012 but I am unable to enable the Integration and Reporting Services
May be I have missed to choose the option during installation.
Basically, I am asking if it is possible to use SSMS (Query Analyzer window) with Informix. I am thinking linked server. If I have the ODBC driver installed on my workstation (win 7 pro VM workstation -- SSMS on same VM machine) is this doable? Do I just do the standard linked server routine or is there a special way (if it is even doable).
View 4 Replies View RelatedI have a login page that checks the given userid/password against ADSI and redirects the user if successful. My problem seems to come when I try to connect to the SQL database with the user's NT credentials. I have the user's NT account setup in SQL, have anony. disabled in IIS, using Integrated Windows Security in IIS, and am using the following connection string:
connectionString = "data source=SqlServer;initial catalog=pubs;Trusted_Connection=Yes"
I also have this entry in my Web.Config file: <identity impersonate="true" />
Actually, it seems to connect OK, but I can not select any data. I just receive "Select permission denied...." errors even though the user has been given these permissions in SQL.
Any advice would be greatly appreciated.
Thanks,
-Ben
Hello,
I am unable to connect to my local sql server express db. I would like to test my app and its not connecting.
It works when I use windows authtentication Trusted_Connection=yes
but not when i use: user id=sdvaid; password=sdavid
Const connectionString = "Data Source=SDAVIDSQLEXPRESS;Initial Catalog=intranet;user id=sdavid;password=sdavid"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand("select * from Users", conn)
cmd.CommandType = CommandType.Text
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString())
Finally
conn.Close()
End Try
thanks.
Here is the error: "SQL Server does not exist or access denied."
Please advise...
Hi all,
I want to distribute my sql server express database file ( .mdf ) with my application. The only connection I can use is the Windows Authentication but I need a Sql Server Authentication with which I can use a username and a password. I tried Sql Server Management Studio Express but no way Can anybody supply a step by step tutorial on how to achieve this? All the examlpes I could find in internet were using Windows Authentication, that's also interesting and painful Thanks for any help guys.
Just spoke w/ our network admin and he mentioned that we use SPI, so being brand new to ASP, i did some googling and found this string:
the username and password is the account i have for the database, which is windows authentication, so i made sure the domain was in there. i am unsure of what i am doing wrong.
set conn = server.createobject("adodb.connection")
Conn.open "Data Source=INTRANETSERV;Initial Catalog=facultyabsences;Integrated Security=SSPI;User ID=domainusername;Password=mypassword;"
conn.open connection
this is the error i receive:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/intranets/absentee/db3.asp, line 3
any help is appreciated.
Hello , i m new to SQL Server when i am connecting it shows me error
"An error has occured while establishing a connection to the server.When connecting to SQL Server 2005,this faliure 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/Instances Specified)(Microsoft SQL Server)"
please help me as soon as possible, Thanks in advance
I'm trying to connect to a database using my virtual machine, where I´m logged in as another user than the one that's granted acccess to the database. Can I enter the windows username and password used to connect to my SQL 2005 database, in Windows Authentication Mode?
Jakob
Hello, I have several SQL 2000 logins on our servers. I removed the builtin/ groups and only have SQL authentication users, although the server is operating in "Mixed Mode". How can I secure this? I don't understand how we can just connect to any DB without ever being asked to log in. Let me know if you can help. Thanks.
I am not able to connect to SQL Database server using windows authentication when I am connected through VPN connection.However I am able to connect to same server using SQL server authentication and also able to do RDP to DB server machine.
I found a resolution in one of the forum suggesting to change UserRasCredentials to 0 value in *.pdk file.Since VPN connection which I am using generates instant proxy this resolution doesn't work.
Also application which I am using does windows authentication connection it is required for me to impersonate windows authetication when connected using VPN.
Hello,
I created a HTTP-Endpoint on SQL Server 2005. Now I try to call this web service from a c# client but using SQL-Server authentication (i.e. my user is a sql server user but not a windows user). But it doesn't work.
I can connect to the service using a windows user and NTLM authentication. But for some reasons Iwould much prefer if I could use SQL-Server authentication. Is it possible?
Here is how I created the endpoint:
Code Snippet
CREATE ENDPOINT myService
STATE = STARTED
AS HTTP(
PATH = '/services',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( SSL ),
SITE = '*'
)
FOR SOAP (
WEBMETHOD 'GetStuff'
(name='MyDB.dbo.FKT_getStuff',
SCHEMA=STANDARD ),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'MyDB',
NAMESPACE = 'Services'
);
GO
Thanks!
Jerma
I could not change user while attmepting to connvert Integration Services using Windows Authentication via MSSQL MS
any idea? thanks
(Also posted on the beta newsgroups...)
View 16 Replies View Related Can someone point me at an article that tells how to allow the
ASP.net worker process to connect, via windows authentication, to a
remote sql server instance ?
Hello everyone,
We have SQL server 2000 on windows 2000. From our desktop MS Access was connected to database through ODBC. Since yesterday (after our consultant had updated another asp application in the same server) we are getting €œSQL Server Error: 18452. €¦.. Login failed for user €˜(null)€™. Reason: not associated with a trusted SQL Server connection. €œ
The consultant saying he did not touch any settings on server (?). And the problem is even server€™s security property shows it has mixed mode authentication. But I cannot connect to server even through my SQL server enterprise manager in windows authentication mode. I can connect if I use SQL server authentication. Through ODBC I can connect also and test fine, if I use SQL authentication. But the Access application wouldn€™t connect using SQL authentication in ODBC.
So, can any one help me to find out what else could be done to make sure that the server is going to accept windows authentication. I€™m thinking any other settings or registry change €¦ etc. Please help.
Thanks
Hi,
I've SQL statement that fail when I execute it on the server. I get the below message:
Msg 50000, Level 18, State 1, Procedure USP_GTL_Transform, Line 226
A transformation of an important table failed
When I execute the sql statement in the SSIS Control flow 'Execute SQL Task" the task succeed and paint in green. When I check in db log its still failing but only presented succeeded in the SSIS
Why the task didnt fail? how can i make it fail and paint in red?
Thanks,
Assaf
We have see login failures for windows accounts. Below is the error message.
Description: In our env most logins are windows accounts. Initially we thought it is an UAC issue and we tried to launch the SSMS using "Run as Administrator". However, we are seeing login failures.
Enviroment:
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
RTM Enterprise Edition (HyperVisor)
Error Message in Error Log :
2015-08-10 22:36:45.290 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:36:45.290 Logon Login failed for user 'domainloginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
[code]....
We tried dropping this account and re-creating the windows account with same permissions but still result is same. It throws same error message. Login failure message !!!
I am just about ready to tear my hair out on this one, at this point you guys are my last resort! Wouldn't it be nice if I could go to bed and have an answer for me in the morning....
I've had a working HTTP endpoint using Basic Authentication over SSL for months in our development environment, but now I'm moving our application to a production server and I'd like to be able to use Digest Authentication in the clear instead so the processor doesn't waste cycles on the SSL decryption. I've spent hours fiddling with my endpoint to no avail.
This is the closest message relating to what I'm doing that I've been able to find: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1131251&SiteID=1
In my case, the SQL Server is joined to a domain that is entirely Windows Server 2003 - no Windows 2000, so support for Digest Authentication is a non-issue. I'm also explicitly doing a GRANT CONNECT on my endpoint to the domain user I'm using. Here is the top of my CREATE ENDPOINT DDL:
CREATE ENDPOINT [Kudos]
STATE=STARTED
AS HTTP (PATH=N'/Kudos',
PORTS = (CLEAR),
AUTHENTICATION = (DIGEST,NTLM),
SITE=N'echobase.kudosnow.com'
DEFAULT_LOGON_DOMAIN = N'kudosnow',
AUTH_REALM = N'kudosnow'',
CLEAR_PORT = 80,
COMPRESSION=DISABLED)
FOR SOAP (
I am debugging using wfetch 1.3... I can get this to work using NTLM Authentication; sending a GET to my endpoint with the ?wsdlsimple querystring parameter returns my WSDL just fine.
But Digest just doesn't want to work! If I remove NTLM from my AUTHENTICATION directive and try using Digest with wfetch or from my (PHP) web service consumer I get an HTTP 401 no matter what I do. I've tried fiddling with the DEFAULT_LOGON_DOMAIN, AUTH_REALM, and anything else I can think of.
The errors I am sporadically finding in the SQL Server error log are:
HTTP authentication failed. [CLIENT: 192.168.245.238] from source Logon
Followed by:
Error: 26026, Severity: 14, State: 1. from source Logon
I can't find any detail about the second error.
........
Just occurred to me that I've also been trying to view my WSDL directly in IE, and this is what's causing the SQL error to be logged, not wfetch - that's why it appeared sporadically.
So, wfetch isn't causing an 'HTTP authentication failed' error, but Internet Explorer is... maybe Digest Authentication isn't implemented correctly in wfetch?
Regardless... I'm still not able to authenticate from IE or from my client, but this may shed some more light on the issue.
Does anyone have any ideas?