Linked Server Loses Login-mapping
May 14, 2007
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Any help is apprecaited...
Thanks
View 1 Replies
ADVERTISEMENT
Feb 29, 2008
Hi Folks SQL2k on NT4 Domain I have a UserDB on SvrA - Access to UserDB is Via a DomainGroup and is assigned db_owner role UserDB Executes a local SP which in turn Executes a remote SP on SvrB via a Linked Server The Linked Server Login Security is via mapping to a remote account which has db_owner on the remote DB. I would like to. a) stop ALL users on SvrA from being able to use this linked serverb) tightly restrict permissions of the remote account to the remote db Is it possible to MAP above local server login to remote server login as the local login is via a domain group. I cannot significantly alter UserDB current Login Security I'm also worried that another system/db is utilizing this Link (legally) but I'm not allowed on the production box to monitor it (Hhrummphh - but unfortunately I have the job of providing scripts to tighten security :rolleyes: - hence the prefference for mapping local to remote users I'm sorry I'm not well versed in SQL Security & this is only a central part of my bigger security job(involving horrible RPC's, dynamic SQL, heteregeounous joins, double hops, delegation etc) Any help appreciated GW
View 11 Replies
View Related
Oct 26, 2007
I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.
Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it.
If I do this it works fine,
EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase'
EXEC sp_addlinkedsrvlogin 'linkedname', 'true', 'AppUser'
But as soon as I add the @provstr parameter, then I get the error if I try to use linkedserver,
EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase', @provstr='Failover Partner=otherservername'
EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedname', @useself='true', @locallogin='AppUser'
It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error.
View 12 Replies
View Related
May 6, 2008
After I run a SSIS package which is a "Trasfer SQL Server Objects Task", the database users has lost all mapping to SQL server login. I've tried various options inside the package, however, to my surprise, sometimes the user settings at SOURCE database is lost! I won't suppose to change anything in the SOURCE database after running a data transfer... Anyone knows what's wrong?
Thanks in advance!
Options:
DropObjectsFirst - TRUE
IncludeExtendedProperties - FALSE
CopyData - TRUE
ExistingData - Replace
CopySchema - TRUE
UseCollation - FALSE
IncludeDependentObjects - TRUE
CopyAllOBjects - TRUE
CopySqlServerLogins - TRUE/FALSE (both tried)
CASE 1:
CopyDatabaseUsers - TRUE
CopyDatabaseRoles - TRUE
Result - the user-login mapping at destination DB is lost.
CASE 2:
CopyDatabaseUsers - FALSE
CopyDatabaseRoles - FALSE
Result - the user-login mapping at SOURCE DB is lost.
View 7 Replies
View Related
Nov 11, 2015
I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437). The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration?
View 6 Replies
View Related
Dec 12, 2006
We are using linked servers to access another server, we are using windows authentication. but when we use
EXEC master.dbo.sp_addlinkedserver @server = 'SERVER', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @locallogin = NULL, @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sa'
When we connect using a SQL login as specified in the above line we are able to connect to the linked server.
But when we want to connect using a Windows user it is connecting.(we want to connect using a specific windows user i.e only one windows user will be there in the server and we shall access the server using that windows user)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @locallogin = NULL, @useself = N'False', @rmtuser = N'Domainuserid', @rmtpassword = 'password'
Is there any other way to connect to the linked serrver.. we dont want to use the SQL server login to connect to the linked server.. how to use windows authentication to connect to the linked server. Anyone have tried it out.. Thanks in advance
View 1 Replies
View Related
Apr 15, 2014
Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB.
ServerB has two databases on it and both using different logins to access. Say DB1 has Login1 and DB2 has Login2
So can I create something like this:
EXEC master.dbo.sp_addlinkedserver @server = N'db_link', @srvproduct=N'SQLServer OLEDB Provider', @provider=N'SQLNCLI', @datasrc=N'ServerB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'Login',@rmtuser=N'Login1',@rmtpassword='Password1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'login',@rmtuser=N'Login2',@rmtpassword='Password2'
Am I able to access two different databases using this same linked server. I tried but with no success.
View 4 Replies
View Related
Jan 31, 2008
I have a stored procedure that retrieves data via a linked server that is running on a local instance of SQL Express 2005. This has been working without problems for some time.
We are now trying to move the database with the links to either SQL Express 2005 running on a server, or an instance of SQL Server 2005. In both cases I can successfully link the servers but when the stored procedure executes it fails on the select statement from the linked server with the following message:
OLE DB provider "SQLNCLI" for linked server "mosier" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Why is the user name null? I've tried explicitly setting login information by running the sp_addlinkedsrvlogin procedure but get the same results. In all cases logins should be using windows authentication and all the servers are inside the corporate firewalls.
I presume there is some property somewhere that is different between the SQL instances that work and the ones that don't but I haven't been able to find anything.
View 5 Replies
View Related
Oct 7, 2014
Is there any way to find out the password for the remote login of the Linked server
View 1 Replies
View Related
Jun 19, 2006
We are having some commercial applications that are running on sql servers at different sites, all with sql security. The software vendor wants to copy data into staging tables on our BI server. But our BI server only support Integrated security.
But how can i say, in the definition of a linked server (at the remote machines) to swicth security system ?
If it is possible i could enter a fixed domain-account in that users fields, but i suppose that this field was intended for SqlServer security.
View 3 Replies
View Related
Mar 12, 2008
Hi all:
I have created a linked server that connects a SQL 2000 database to a SQL 2005 database. If I use individual SQL or Windows accounts as local logins on the SQL 2000 instance, I can successfully query the linked SQL 2005 database.
(For security we use the setting "For a login not defined in the list above, connections will: not be made")
If I try to use a Windows group as the local login, remote queries fail with the error
"Access to the remote server is denied because no login-mapping exists"
Is it not possible to use a Windows group for the local login of a linked server?
If I run 'exec sp_linkedservers' the setup appears valid
Linked Server Local Login Is Self Mapping Remote Login
SQL2005Serv DomainBRubble 0 SQL_Read_Access
SQL2005Serv DomainWindows_Group 0 SQL_Read_Access
SQL2005Serv DomainFFlintstone 0 SQL_Read_Access
Thanks in advance
Grant
View 5 Replies
View Related
Aug 3, 2007
Hello All,
My application is dependent on two databases one is on sql 2005 machine and other is on sql 2000 machine. A linked server is establish between these two using sql account which has appropriate rights on both server's databases.
application works fine but after some time it crashes and if i see error from sql profiler i get the error
OLE DB provider "SQLNCLI" for linked server "sql2000machine" returned message "Login timeout expired".
Few days back applciation was working absolutly fine, but i was trying to make linked sever through windows account, i dnt know wat has i changed during achiveing that setup that now application (linked server) is not working even with sql account.
please help its urgent.
View 2 Replies
View Related
Aug 3, 2007
Hello All,
My application is dependent on two databases one is on sql 2005 machine and other is on sql 2000 machine. A linked server is establish between these two using sql account which has appropriate rights on both server's databases.
application works fine but after some time it crashes and if i see error from sql profiler i get the error
OLE DB provider "SQLNCLI" for linked server "sql2000machine" returned message "Login timeout expired".
Few days back applciation was working absolutly fine, but i was trying to make linked sever through windows account, i dnt know wat has i changed during achiveing that setup that now application (linked server) is not working even with sql account.
please help its urgent.
View 1 Replies
View Related
Sep 1, 2015
I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "
Same problem facing on different servers.
Note : Environment is SQL 2012,SQL 2008 including cluster servers .
View 2 Replies
View Related
Mar 23, 2006
I get this error when trying to alter a stored procedure that has an insert statement referencing a new linked server I created:
INSERT INTO [servername].databasename.dbo.DirectReport
...
Msg 18456, Level 14, State 1, Procedure Get_Direct_Pay_Move_Data, Line 17
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I added Administrators and my logon to the permissions of my linked server but still get this error when it tries to save my stored proc...the one which has that insert.
View 4 Replies
View Related
Sep 25, 2003
For no reason that I can see my SQL Server enterprise manager keeps losing all its SQL Server registrations. Enterprise Manager retains the top-level groups.
I am running enterprise manager on XP.
Help!
:confused:
View 2 Replies
View Related
Dec 1, 2004
We have a view in a 9205 oracle database. We can query fine
and the decimal precision is there.
When we query this same view from ms sql server we lose the precision
so 115.25 becomes 115.
does anyone know a workaround for this?
View 2 Replies
View Related
Mar 19, 2008
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Any thoughts!
View 5 Replies
View Related
Jun 17, 2006
After a period of use (typically 1-2 hours), an application using SQL Server Express stops writing to the database, whilst still continuing to read from it. As far as I can tell, no error message is generated by any operation. The application is written in VB Express and is executed on several machines, with SQL Server Express on a central machine. Re-starting the application on the affected machine restores normal operation.
The problem on one machine does not cause a similar problem elsewhere at the same time (hence it appears to be an issue with the relationship between the application and SQL Server)
This does not appear to relate to the response to Q asked by sgcook on 9 may as we are not using user instances, but only a bare minimum connection string.
View 15 Replies
View Related
Jul 30, 2015
I have a SQL 2014 SP1 set of servers with two asynchronous copies of an availability group. One of the asynchronous sites is down and SQL can no longer replicate the changes. I need to understand how long SQL Server can continue this way before the secondary replica will no longer be able to catch up. I assume this is really tied to the transaction log on the primary replica but would like it clarified.
View 4 Replies
View Related
May 16, 2007
Here's a weird one:
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid."
If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
Or am I missing something?
Mike
View 5 Replies
View Related
Jul 17, 2007
Server1 sa password is password1
server2 sa password is password2
I can't execute sql with linked server tables because the login fails for sa.
When I setup a login with the same password on both servers, it's fine.
Is there a way to save the password?
I used sp_add_linked_server to generate the link between them.
Server: Msg 18456, Level 14, State 1, Procedure InsertOutstandingInvoices, Line 6
Login failed for user 'sa'.
the line it fails on is where I have qualified the server name:
[server].[db].dbo.[tablename]
View 3 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
View 6 Replies
View Related
Apr 16, 2008
Hi all,
Have just tried my sql server 2005 connected asp classic website on II7 for the first time and have gotten some errors.
First off I have unchecked show friendly errors in IE and have enabled ASP to send error messages to the browser.
My error I get is when I execute a basic .asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed.
/index.asp, line 10 which is dbConnect.Open cstring
from this peice of code:
sub dbOpen()
set dbConnect = Server.CreateObject("ADODB.Connection")
cstring = "DSN=mysqldsn; Trusted Connection=yes"
dbConnect.Open cstring
end sub
I have gone into ODBC and have setup both a user dsn and file dsn that equals mysqldsn and points to my database (I know you only need to set up one DSN but I'm not sure which)
I also notice under mywebsite home panel there is a connection string option. Do I do anything with that?
Definatley a lot more to it than XP's II6!
Any help or advice would be greatly appreciated.
View 3 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Oct 23, 2007
Is it normal that my scd loses its mappings if I change one of its inputs?
When I talk about mappings, I talk about the first page of the scd wizard. I set everything (all the columns, business keys) and run the wizard until the last step. SCD creates two outputs (Changed and New Rows). All Ok.
If a column I use as input in the scd changes (for example, it is not a conversion column any more but a derived with the same name), my scd loses all the mappings!
It is very annoying since I have to map all of the columns again. Is this a normal behaviour of the scd or I' doing something wrong? Is there a way to 'repair' my mappings without needing to set one column at the time every time?
View 3 Replies
View Related
Aug 22, 2006
Hello, I am developping a web application who is connected to SQLServer 2005 via the connection sring as below
<add name="LocalSqlServer" connectionString="Data Source=IBM7;Initial Catalog=Reclamation;Integrated Security=True "
providerName="System.Data.SqlClient" />
I want to know which user (bydefault ) who is connecting to the DB . and how is mapped this user with user in SQL server?
Thanks in advance
View 3 Replies
View Related
Jul 18, 2000
I have a question regarding BCP. We are doing a BCP operation from one machine to another machine. (i.e) Flat file from the web server are BCP on to the DB server. For this operation we have mounted the FTP directory of the web server on to the DB server. Say G: drive of the DB server is mapped to the Ftp directory of the web server.
Now when we run the BCP we get the following error.
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create File()).
The connection is using NAMED PIPES.
Say when we BCP out the above error message comes but the flat file is generated with out any records in it.
Are we missing any parameter settings.
Please help.
View 1 Replies
View Related
Sep 10, 2001
Hi,
I've had a workstation running Windows NT 4.0 and SQL 7.0 successfully and
this has just been upgraded to Windows 2000. Althogh the s/w is still on
disc in the correct plance, SQL Server is no longer in the Program group
from the Start menu, the SQL Server servicess aren't visible in Service
Manager and the SQL Enterprise Manager icon in the folder produces an MMC
error I don't recocnise or understand - Help ! What's happened where and
does anyone know how I resolve this
Thanks in advance
Dominic Nudd
View 4 Replies
View Related
Sep 30, 2006
Hi
When I deploy a database (mdf and ldf) and attch the database I get errors when my stored procedures attempt to call CLR stored procs.
Error regards database trust and assembly safety etc. This works on my development system/database so it's setup correclty there.
Clearly I'm missing some additional setup when attaching the database to another server instance. Can't figure it out though. I'm marking the assembly UNSAFE (it requires network access) and the database as trustworthy when attaching but the error persists. Also making sure the server instance has CLR enabled.
What am I missing? Any ideas?
View 9 Replies
View Related
Jul 31, 2007
We have both a custom data extension and a custom authentication extension for Reporting Services 2000.
Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.
I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.
Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:
Code Snippet
Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo
If Not System.Web.HttpContext.Current Is Nothing _
AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then
userIdentity = System.Web.HttpContext.Current.User.Identity
Else
userIdentity = Nothing
End If
userId = IntPtr.Zero
End Sub
Any help or insights would be GREATLY appreciated - thanks, Ken.
View 8 Replies
View Related
Apr 26, 2015
Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.I've searched around and I don't think MS SQL can do this...I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.
However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks.On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID...
View 2 Replies
View Related
