Current Security Context Not Trusted When Using Linked Server From SAP
Dec 31, 2006
Hello,
I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.
The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.
Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."
I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).
What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).
Any insight someone could offer on this thorny problem would be most appreciated.
Best regards,
Matt
View 12 Replies
ADVERTISEMENT
Jun 27, 2007
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
To everyone, thanks for reading!
~ Delora
View 2 Replies
View Related
Aug 25, 2006
Hello,
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
The remote database is set to Trustworthy.
I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
Thank you for your help on this - Amos.
View 17 Replies
View Related
Jun 27, 2007
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
To everyone, thanks for reading!
~ Delora
View 1 Replies
View Related
Feb 10, 2014
We've got 3 SQL Servers all SQL Server 2012. We have a Master server and 2 production servers (let's call them A and B). The Master has linked servers defined for both A and B in an identical way.
A and B both have the same databases and each has a Stored Proc that gets called by the Master server.
An SQL Agent job executes the stored proc on both A and B.
It works fine against A but not B.
If we execute the stored proc in a query window under our windows account (domainowner) it works, if we use SetUser to pretend to be the SQL Server Agent account (let's call it domainagent) A works and B doesn't.
Executing against B returns the error "Access to the remote server is denied because the current security context is not trusted."
domainagent is a sysadmin on both Server A and B.
Trustworthy is set to ON in both the Master Server Database it's executing from and the remote databases on both A and B.
All 3 databases have the same owner (let's call it domainowner).
View 5 Replies
View Related
Aug 25, 2006
Remus (or anyone who can help) -
I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
Thank you for your help on this - Amos.
View 1 Replies
View Related
Sep 12, 2007
Hi,
I have a stored procedure servicing a queue (sql 2005).
The stored procedure updates some data in a different server and database.
If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all.
However, if I set the queue to use the same stored procedure, it gives me the following error:
Error 15274: Access to the remote server is denied because the current security context is not trusted.
Any ideas?
Thanks,
Aron
View 4 Replies
View Related
Nov 9, 2007
From my desktop in Management Studio (MS) I can connect to server A and run a query like,
select top 1 * from serverB.mydatabase.dbo.mytable, and all seems to be working. If I leave and come back after a few minutes it no longer works and I get this error:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
If I remote connect to server B and run the query it works and when I try it from my desktop MS it starts working again. Any ideas on why this is happening?
View 8 Replies
View Related
May 23, 2007
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
View 17 Replies
View Related
Jun 16, 2007
I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
View 6 Replies
View Related
Feb 7, 2008
I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.
when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,
The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)
Any idea???
Thanks,
View 7 Replies
View Related
May 1, 2007
Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message
The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.
This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.
I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?
View 5 Replies
View Related
Oct 21, 1999
SQL Server in on a ‘member’ server in my company domain (We took the ‘stand-alone’ option when installing NT on this server).
I have set up an NT domain account for SQL ServerAgent ‘Service startup account’ which is a different account than the NT domain account listed in the SQL Server Properties, Security tab, ‘Startup service account’.
I log on to this server with the login in the latter.
Replication is working OK, but my application log keeps filling up with the message “SQLServerAgent security context does not have server autorestart privileges”.
What have I done wrong?
Thanks,
Judith
View 1 Replies
View Related
Dec 7, 2000
Does anyone know of a component or other mini application that can be run on the IIS server and allow IIS to access SQL Server 7 databases using Trusted Security.
Our security unit has required us to run SQL Server with Trusted Security so changing to Mixed mode is not an option. We are also not allowed to use Basic Authentication for Active Server Pages with IIS. IIS and SQL server are also on different machines.
We are looking into the possibility of trying to create a component that would run on the IIS machine to allow us to use Trusted Security to the SQL Server (in effect try to make it work the same way that the Cold Fusion product does).
Any help would be really appreciated.
Thanks
View 1 Replies
View Related
Oct 16, 1998
I am writing a trigger to audit changes to certain columns. In the trigger I
would like to record to the "Audit" table as much information about the
current execution context as possible (current user, login, nt user,
inputbuffer, etc.). I couldn`t find the way to find out what is the
currently executed* stored procedure. @@PROCID returns object id for the
trigger itself. Any help would be highly appreciated as this is an urgent
production environment issue.
regards,
Anatol
View 1 Replies
View Related
Nov 3, 2005
I am using sql 2005 beta
View 23 Replies
View Related
Jun 19, 2015
Any way to find out who's causing "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." error in SQL Server error logs?
View 7 Replies
View Related
Oct 20, 2006
Hi,
I have a piece of Java code that needs to connect to SQL 2000 (SP4) using Windows Authentication. It's running on Windows Server 2003 SP1.
I tried JDBC v1.1 and followed the code from the following blog:
http://blogs.msdn.com/angelsb/default.aspx?p=1
But still get this error as shown below. Any help appreciated.
I am using JDK1.4.2, "sqljdbc_auth.dll" is located under "E:SQL2005JDBCDrvsqljdbc_1.1enuauthx86", also made a copy under "E:JavaTest" and "C:WindowsSystem32" but still won't work.
Cheers
Allan
===========================================================
E:JavaTest>javac -classpath ".;E:JavaTestsqljdbc.jar" TestW2.java
E:JavaTest>java -classpath ".;E:JavaTestsqljdbc.jar" -Djava.library.path=E:S
QL2005JDBCDrvsqljdbc_1.1enuauthx86 TestW2
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.processLogon(Unknown
Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source
)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknow
n Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover
(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at TestW2.main(TestW2.java:7)
===========================================================
The code is simple (TestW2.java):
import java.sql.*;
public class TestW2 {
public static void main(String[] args) {
try {
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = java.sql.DriverManager.getConnection("jdbc:sqlserver://VMW2k3ENT003.TESTCBFPOC.COM.AU;integratedSecurity=true");
System.out.println("Connected!");
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
==============================================================
View 27 Replies
View Related
Jan 26, 2007
Hi,I've got some strange problem with my project. It was working, but then I made some minor changes (like adding menu) which have nothing to do with the code and I started to get that error: Description: An
error occurred during the compilation of a resource required to service
this request. Please review the following specific error details and
modify your source code appropriately.
Compiler Error Message: CS0103: The name 'SqlDataSource1' does not exist in the current context
Source Error:
Line 22: Guid _userKey = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in userLine 23: //insert instruction for table messageLine 24: SqlDataSource1.InsertCommand = "insert into [message](useridsender,userIdreceiver,message,Sendername, ReceiverName) values('" + _userKey + "'" + "," + "'" + DropDownList1.SelectedValue + "'" + "," + "'" + TextBox1.Text + "'" + "," + "'" + _membershipUser + "'" + "," + "'" + DropDownList1.SelectedItem + "');";Line 25: SqlDataSource1.Insert();Line 26: What does it mean? The names are correct, but the part with code (ie. something.aspx.cs) acts as if it has no information about the aspx file itself. How can I repair it?Regards,N.
View 2 Replies
View Related
Jul 20, 2015
I have 2 stored procedure, insert and update.
They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.
Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;
Then I would expect the table difference to not matter as the table is being dropped...
View 2 Replies
View Related
Jul 21, 2015
I can't seem to upload images so here's the link:Â [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.Â
View 8 Replies
View Related
Oct 5, 2006
I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.
View 5 Replies
View Related
Feb 2, 2004
Hi,
We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.
"SQLServeragent security context does not have server-autorestart privileges"
Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?
Ted
View 1 Replies
View Related
Mar 23, 2008
I have two questions.
How can I run my C++ assembly in safe mode?
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
View 4 Replies
View Related
Mar 23, 2006
some time back i had problem with sending the service broker messages on remote machine,
I had some security issues and they were resolved by the help of Remusu.
since the ip of the remote machine was changed in between so i just re-executed the same scrips which used to successfully send messages on the remote machine.(I just updated the IP in the route )
To my surprise same script did not work now where as no change have been made.
I am doing the following:
1.Created the certificate and end point on the sender side. back up the certificate in a file and copied to the other machine.
same step was repeated for receiving side as well.
2.both the side i created the certificates using the back up files from other sidend proper authorization
3.Then I created the database,route,messagetypes,contract,queues,services etc both the side.
4. then i setup dialog security(ie.created the dialog security certificates both the sides and back up them). Later I copied these back up files to each other, create some dialog user and create certificate using authorization to these remote dialog users created.
5.I also created remote service binding on both the sides and granted send permission to the remote dialog user.
When i send the message from sender to the receiver, and run the profiler, I see that on the sending side none of the broker event gives any error.
In the recever side I get the followng Event:
Broker:Message Undeliverable
This message could not be delivered because the security context could not be retrieved.
Error 11229.
I m surprised that the same script was run in the same order,Why was it running before and not now.
I also checked the End points using telnet and they seem to be fine. Also the firewall was "Off" on both the machines i.e. there was no change in system state also.
Please provide the solution. Thanks in advance.
View 12 Replies
View Related
Jan 15, 2008
I am trying to run the following set of commands on our SQL 2005 SP2 server:
declare @cmd varchar(1000)
select @cmd = '%SystemRoot%system32cscript.exe %SystemRoot%system32iisvdir.vbs /create TestSite testvirtualdir C:Inetpubwwwrootfiles'
exec master..xp_cmdshell @cmd
The objective is to programmatically create an IIS virtual directory from within a stored proc. I already have a proxy account configured using the sp_xp_cmdshell_proxy_account proc. The SQL server is a member server to a domain and the proxy account is a member of Domain Admins (per iisvdir.vbs requirement). When I login to the server as the proxy account and run the dos command in a window it executes just fine. However, the above SQL statements result in the following in server management studio:
"You cannot run this command because you are not an administrator on the server you are trying to configure."
If the xp_cmdshell runs under the proxy account, and the proxy account has been verified to be able to login and run the script independently, what gives?
Edit:
Just to make sure the proxy account was setup properly, I used the following cmd
select @cmd='set'
and it's results set shows
USERNAME=sqladmin
which is indeed the name of the proxy/windows account that I can run the dos script under when logged into windows...
View 5 Replies
View Related
Apr 13, 2007
HI
I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found
On the Target Server:
on Broker: Message undeliverable --- This message could not be delivered because it is a duplicate
On the Source Server
on Broker: Message undeliverable --- This message could not be delivered because the security context could not be retrieved,
I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this
conversation_handle to_service_name is_end_of_dialog message_body transmission_status
E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL
I reinstalled the broker several times, but always get this problem.
thanks
Paul
View 9 Replies
View Related
May 29, 2007
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
Thanks,
Jon
View 9 Replies
View Related
Oct 4, 2007
Hi,
I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:
EXECUTE AS LOGIN = 'link_user'
SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)
But when I use the exact same statements in a stored procedure I get the folllowing error:
Msg 7416, Level 16, State 1, Procedure linktest2, Line 5
Access to the remote server is denied because no login-mapping exists.
I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?
Is there another workaround then to create all users in the linked server security?
Rgds
Bertrand
View 1 Replies
View Related
Jun 5, 2015
I have 2 databases one for datawarehouse and one for reporting.
I created a view in the reporting database joining several tables from datawahourse. The view is meant for a users who uses powerpivot. It will contain a few million of rows.
I set up a role in the reporting database. I assigned select rights to that role on the view.
When the user wants to access the view, he can see the view but a select gives
The server principal "pp_user" is not able to access the database "datawarehouse" under the current security context.
How can I fix this ? I don't want to give the user select rights on tables in the datawarehouse.
View 3 Replies
View Related
Jan 19, 2007
I have setup a linked server on the same computer but different instances of sql. When I call the sprocs an error occurs... The message is...
The OLE DB provider "SQLNCLI" for linked server LINKEDSQL does not contain the table ""product"."dbo"."AccountTable". The table either does not exist or the current user does not have persmissions on that table.
I've checked the table "AccountTable" and it does exist. The database exist also which is "product". I also configure the linked server's RPC to "true" and timeout to "200". The linked server is also configured to use a single username and password which exist on the instance of sql that the linked server is connecting. Also, I enabled the instances of sql to allow remote connections and use mixed authentications.
The mode of access is that different client pc will call a method created with .NET, which in turn, call the sprocs on the linked server. Should I add all the client pc that will be calling the method? Any help or advice please... Thanks
View 5 Replies
View Related
Jan 17, 2007
Good afternoon,so here is a question I've been struggling with that I thought folks onthe forum might have some wisdom kicking around on. I have severalproduction servers that are living within a workgroup where there is nodomain controler. However, I would still like to set up trustedsecurity for my asp.net applications. IIS is living on one server, andSQL server on another. Is there a way I can add a trusted relationshipto the machine and account that the IIS app is running under?My thanks in advance!Jonathan Wiggs*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Apr 13, 2007
What is the practical difference between using "Not be made" and "Be made without using a security context"?
I've searched and it seems that if you are serious about NOT wanting anyone except a single login to be able to access the linked server that you ought to use "Not be made", however it seems that elsewhere it suggests that "Be made without using a security context" will accomplish the same thing.
Is that right?
If so, then what is the point?
Ex: I'm trying to apply the fewest settings on a sql server B linked to sql server A, but I only want login ZZZ to be able to access B through A, so I figure I ought use "Not be made", however reading various places it seems that "Be made without using a security context" is suggested to do the same thing.
This quote from -
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx
"Be Made Without Using A Security Context Blocks access to all logins not explicitly mapped to the linked server."
View 4 Replies
View Related
