SQL 2k Linked Server Login Mapping For NT Group Accounts
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 server
b) 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
ADVERTISEMENT
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
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
Apr 16, 2008
Hello all,
I have 2 networked PC's both running vista ultimate
1st is Laptop and is running its own SQL Server at laptoplaptopSQL
2nd is Desktop and is running its own SQL Server at desktopdesktopSQL
Now both machines have seperate windows login accounts.
When I go SQL Server management studio I go to browse and each machine can see the other machines SQL Server, but when I go to login I get SQL Login falied for users" The user is not associaed with a trusted SQL server connection".
So I then go to logins new login and try to add my other pc's user account.
The problem I see is that when I go to search and then location it only shows its own PC's location and not the location of my other networked pc? So if I am on Desktop and in my theory want to add laptopuser to the desktop SQL Server logins I get:
"create failed for login laptopuser
An exception occurred while executing Transact SQL statement laptopuser is not a valid windows NT name. give the complete name
Not sure on where to go from here.
Any help would be great
View 10 Replies
View Related
Sep 12, 2006
My question is I have a SQL Server running on Web Server which is a member of a 2000 Active Directory, I only grant access to the database via Global Groups from the Active Directory. When I log onto the database via Windows Authentication the actual user shows up in the master.dbo.sysprocesses table, I can tell what database that process is going to but not how that user is being translated to the Global Group that was actually given access. I need the actual database user name which is the Global Group name that had permissions granted via user defined database roles so that I can do some pre-processing in an ASP.NET application so that I know what parts of a form are updatable or not.
View 1 Replies
View Related
Jun 5, 2015
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
View 9 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
Jul 23, 2015
Do we still need the below service accounts in SQL 2008+ version even if we have proper SQL service accounts added in the logins?
[NT AUTHORITYSYSTEM]
[NT ServiceMSSQLSERVER]
[NT SERVICEReportServer]
[NT SERVICESQLSERVERAGENT]
[NT SERVICESQLWriter]
[NT SERVICEWinmgmt]
View 0 Replies
View Related
Sep 15, 2000
When creating a login account, it is associated with a default database.
Is it then necessary to grantdbaccess to the default database?
View 1 Replies
View Related
Jun 12, 2007
I have a SQL2005 in a cluster environment, for some reason the only way that user accounts can login to either the database or SSMS is to grant them the SysAdmin role. This access is a little to high for my liking and am wondering if anyone else has come across this before.
Thank you
View 15 Replies
View Related
Dec 19, 2006
Hello,
I'm having a problem using Windows Accounts to login to a SQL 2005 Server.
Here is my setup. The SQL server and web server are separate machines. I'm also not developing directly on the web server.
SQL Server - Windows 2003 Server- SQL 2005- Set to use SQL and Windows AuthenticationWeb Server- Windows 2003 Server- IIS 6.0 - Anonymous Authentication is disabled - Integrated Windows Authentication is enabledApplication web.config:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"><appSettings> <add key="ETR_Environment" value="Dev"/></appSettings>
<connectionStrings> <add connectionString="Data Source=sql-dev-server, 1179;Initial Catalog=ENV_ETR;Integrated Security=SSPI;" name="ETR_Dev"/> <add connectionString="" name="ETR_Prod"/></connectionStrings>
<system.web> <compilation debug="true" strict="false" explicit="true"/> <pages> <namespaces> <clear/> <add namespace="System"/> <add namespace="System.Collections"/> <add namespace="System.Collections.Specialized"/> <add namespace="System.Configuration"/> <add namespace="System.Text"/> <add namespace="System.Text.RegularExpressions"/> <add namespace="System.Web"/> <add namespace="System.Web.Caching"/> <add namespace="System.Web.SessionState"/> <add namespace="System.Web.Security"/> <add namespace="System.Web.Profile"/> <add namespace="System.Web.UI"/> <add namespace="System.Web.UI.WebControls"/> <add namespace="System.Web.UI.WebControls.WebParts"/> <add namespace="System.Web.UI.HtmlControls"/> </namespaces> </pages>
<authentication mode="Windows"></authentication> <customErrors mode="Off"></customErrors> <authorization> <allow users="XXXWilliam.Klein"/> <deny users="*"/> </authorization></system.web></configuration>
The reason why I want to use the windows login to connect to the database is the application needs to keep track of who did what when entering and updating data but still keep them using there windows login accounts. So using a generic account will not work.
What keeps happening is I keep getting this error: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. When trying to connect the database. I've tried this on two web servers on another I get something slightly different: Login failed for user 'XXXWeb-Server$'.
Anybody able to give me any suggestions on how to fix this?
View 12 Replies
View Related
Dec 21, 2000
Is it possible to rename a SQL Login or User account?
View 1 Replies
View Related
Aug 12, 2015
We are seeing 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]
2015-08-10 22:41:23.470 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:41:23.470 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]
Troubleshooting done:
- Recreated the windows login in sql server. Doesn't work.
- ran sp_valdidatelogins. it doesn't return any rows.
- I belong to sysadmin role and when I say, getting below error message.
xp_logininfo 'domainloginname'
/*
Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 64
Could not obtain information about Windows NT group/user 'domainloginname', error code 0x5.
*/
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 !!!
View 18 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
Dec 20, 2000
I have jsut started using SQL server 7 and am having problems with accounts permissions, users,roles, groups, owners etc what are the differences?
View 1 Replies
View Related
Jan 18, 2008
I'm attempting to write a script that I can execute accross 30 servers that will create a domain login and subsequently grant access to said account on all databases per server. The only problem that I'm running into is trying to dymanically create the login. Example source is below.
declare @sql varchar(1000)
declare @loginname varchar(50)
select @loginname = 'DOMAINaccountname'
set @sql = 'if not exists (select * from master.dbo.syslogins where name = N' + char(39) + 'DOMAINaccountname' + char(39) + ')' + char(10) + char(13)
set @sql = @sql + 'begin ' + char(10) + char(13)
set @sql = @sql + char(9) + 'exec master.dbo.sp_grantlogin ' + quotename(@loginname)
print @sql
exec (@sql)
Here is the generated output and the error. Any suggestions would be appreciated.
if not exists (select * from master.dbo.syslogins where name = N'DOMAINaccountname')
begin
exec master.dbo.sp_grantlogin [DOMAINaccountname]
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'DOMAINaccountname'.
View 4 Replies
View Related
May 24, 2015
I am just wondering if i can control the access of range of variables from users' logins.
To be more specific, if i a parameter alpha has five values (1,2,3,4,5) and then i have 3 users A, B C, what i need is:
1. A can access Vaues(1, 3)
2. B can access Values (2, 4)
3. C can access Values(3,5)
Is there anyway i can do this either from the stored procedure i am getting the data or i can do it on SSIS development side?
View 4 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
Nov 17, 2000
Hello, I do not succeed in authorizing the access of a group Windows NT to a linked server, on the other hand if I specify user's name it works.
Please inform me.
Pascal
View 1 Replies
View Related
Feb 11, 2014
I setup SQL Server 2012 on Windows Server 2012 with the service accounts in the local Administrator group, but now that I'd like to remove the accounts from this group I'm finding they don't have the appropriate access to the network storage. notes on setting the per-service SID's for SQL (SQL Engine, Analysis Services, Reporting Services, and Agent Service) so they can read the Data, Log, and TempDB mount points?
View 2 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
Jan 19, 2006
Hi,
I am trying to add "Remote Desktop Users" as a login group to SQL Server 2005 (i.e. so they can log into management studio). I get a "Not Found Error" (see below). But it is of course found, because I selected it :-) What is going on here? I see the "Administrators" group and various SQL Server groupls added automatically there, so there must be a way, right?
I know I can add each user in the group individually, but that seems overkill when I can just use the group in which they are contained.
Here is the error I get:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Login 'TESTSERVERRemote Desktop Users'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Windows NT user or group 'TESTSERVERRemote Desktop Users' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 6 Replies
View Related
Jan 5, 2008
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
View 1 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
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
Aug 12, 2015
I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal."
View 8 Replies
View Related
Apr 30, 2015
Need script for below.
1)Add the user ''ADabc' to local admin group in server.
2)Create login 'ADabc' and Grant sysadmin access for ADabc
View 3 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
