Linked Servers And Windows Authentication
Jan 23, 2006
I have seen similar questions, but none of the answers have solved my problem.
I have several SQL2000 servers running under Win2003. In the past, we have been using SQL logins, but I have been trying to transition to using Windows Authentication. It has been working great except that sometimes queries using linked servers fail.
The linked servers are set up for the connection to use the login's current security context.
For testing this problem, I have limited myself to two servers to do this between. My Windows account is defined with sa rights on both servers. I have tried explicitly to impersonate my account, I have told it to explicitly use the same account and password on the other server. I have tried not listing my account and letting it default my original setting of using current security context. (if I tell it to use the sa account remotely, that does work)
Both SQL Servers run under the same domain account. That account had delegation rights. My account is not marked as sensitive. The computer account has delegation rights. There is a Service Principle Name defined for both servers.
I have tried everything I can find to try, yet I still get the following message (or something similar, depending on which settings I have changed around).
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
What am I missing?
View 41 Replies
ADVERTISEMENT
May 9, 2007
Hi
I am using SQLXML 3 on a Windows 2003 server Running IIS 6.0. I used SQLXML configure IIS Support to create a virtual directry on this server to connect to MS SQL Server 2000 instance running on another windows 2003 server.
Configurations for the virtual directory are
1. Used Windows integrated authentication.
2. Created template for executing the stored procedure.
When i try to access the DB Server through a browser i am prompted to provide for Windows authentication. Even if i provide the domain user that has administrtive rights on both the server i am getting a response.
ERROR: 400.100 Bad Request
HResult: 0x80046000
Source: Microsoft SQL isapi extension
Description: Invalid connection settings: access denied
If from MMC i change the properties of the virtual directry to enable anonymous acess using the cridentials that i mentioned earlier, everything is working fine. I have been searching the net for 2 days and tried everything that i could find but still i keep getting the same error.
Any help will be highly appretiated.
View 3 Replies
View Related
Jun 10, 2007
Using Bulk Insert Task extensively in our solution. Everything was working great till we deployed it in stage columns. The database server is different from application servers. We have ASP.NET web services driving SSIS packages on application server. After struggling thru several security issues to get this working (ended up creating an application pool with a domain account) we are now stuck with this problem. On a different note still don't understand what specific security permission is available to domain account that makes it work.
Read in some blog that SQL Server 2005 SP2 Beta had this (Bulk Insert) fixed but not in final production version. Is there a specific reason why this is so?
SSIS and the API is quite easy to work with but associated security and deployment issues are not always clear. A lot of answers seem to be coming from end users - thanks a lot to all for sharing your experiences - sadly not presented clearly in SSIS documentation.
View 2 Replies
View Related
Sep 24, 2015
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
View 9 Replies
View Related
Sep 9, 2015
I have two SQL Server machines - RPTPROD and DATAPROD.
I wanted to create a linked server from RPTPROD pointing to DATAPROD using Windows Authentication. When I tried to create this linked server, I keep getting this error
"Login failed for user 'NT AUTHORITYANNOYMOUS LOGON' (Microsoft SQL Server,Error: 18456)".
On the "Security" page, I chose "Be made using the login's current security context". I'm sysadmin on both SQL Server machines.
View 3 Replies
View Related
May 15, 2007
I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".
The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.
In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.
So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.
Does anyone know why?
View 10 Replies
View Related
Nov 14, 2006
I have installed SQL 2005 (X64) on 64-bit Windows 2003 server (AMD). One of my databases need to connect several Oracle databases on 32 bit servers.
I managed to install Oracle 32 bit Client (was unsuccessful with 64 bit installation) and could set up the odbc (32). But I coul'nt find Oracle under linked servers!
Does anyone know what I need to do?
View 3 Replies
View Related
May 11, 2007
How to give authentication for Send Mail Task component?
View 4 Replies
View Related
Apr 18, 2007
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies
View Related
Mar 25, 2004
Hello,
(Using win2k, sqlserver2k, framework 1.1)
I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
Any ideas??
View 2 Replies
View Related
May 15, 2006
Hi all,
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
Thanks.
View 1 Replies
View Related
May 12, 2006
Hi all,
My work is using a shared application
which accesses a MSSQL 2000 database. To access the application, the
folder on the Windows 2003 Server is shared and users can access the
folder through a shared drive.
For the application to access the
database, it uses an ODBC connection to the MSSQL server which
originally used the SA password.
We have recently switched to using
Windows Integrated Authentication because we believe it offers a
higher level of security. However the only way in which we have been
able to enable this is to add the windows users to the SQL server.
The problem with this is that the
application sets permissions for individual users on what records
they can see within the database. We have found that by adding the
windows users to the SQL Server, they can bypass the permissions the
set by the application by simply using any application that can use
an ODBC connection, such as Enterprise Manager, and see all the
database.
One way around this would be to set up
domains of users with access privileges to the tables which reflect
the permissions set by the application, and configuring a view of the
data so they may only see the records that they have permissions to.
However to do this would require a high administrative cost to ensure
that changes made in the application are reflected in the privileges
of the SQL server.
Instead, is there a way the SQL server
can authenticate that the ODBC connection is coming from the correct
application using Windows Integrated Authentication?
This would allow the applcation to
determine security, and stop users from connecting to the SQL server
using other applications.
Alternatively, can the SQL server,
using Windows Integrated Authentication, also ask the application to
supply a username and password?
Any help with this matter would be
greatly appreciated.
Thanks!
View 4 Replies
View Related
Mar 26, 2008
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
View 3 Replies
View Related
May 8, 2003
Hello
Can anyone tell me what is the difference between sql authentication and windows authentication.
Examples of each would be very useful
Many thanks in advance
Steve
View 2 Replies
View Related
Oct 16, 2006
Would anyone please help me out here. which of the 2 modes of authentication is better and why??
View 3 Replies
View Related
Dec 18, 2006
Hi,
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
Thanks
Santhosh
View 1 Replies
View Related
Mar 12, 2008
For using different services of SQL SERVER 2005 which is better...
Windows Authentication or SQL Server Authentication?
what are the advantages and disadvantages of both?
View 5 Replies
View Related
Feb 1, 2008
Hi.
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?
View 1 Replies
View Related
May 31, 2007
I am in the process of rolling out a sql server 2005 enterprise install and had a question regarding authentication. We will be providing sql hosting for a number of groups on our campus, many who are not using our campus-wide active directory, though they all have an AD account.
Windows authentication via the management studio appears to use your AD authentication tokens and will not allow them to enter a username/password combo. Is there any way to configure this?
I would like to use our campus AD for obvious reasons but if there is a requirement for passing tokens this isn't going to work right? It's also going to make database mirroring more of a challenge.
Thanks
View 3 Replies
View Related
Oct 24, 2006
My SQLExpress seems to be set for windows authentication and not sql server authentication ... I am not quite sure why it is set to windows authentication - I installed this with Visual Studio 2005 and it just seemed to be set that way.Am I able to change this - if I choose sql authentication when connecting I get an errorIf someone could answer this point I would be most grateful !!Patrick
View 1 Replies
View Related
Sep 30, 1999
Hello,
I'm trying to use Enterprise Manager in Windows 95, and attempting to
connect to SQL Server with Windows NT Authentication but get message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server Connection.
I can connect okay using SQL Server Authentication.
How can I connect using Windows NT Authentication?
It's SQL Server 7 and protocol TCP/IP I'm using.
Any help would be appreciated.
View 3 Replies
View Related
Nov 24, 2003
In an effort to eliminate the need to code accounts and passwords into the VB Com calling stored procedures, I would like to use Windows Authentication. I have setup the account that the com is running under to have access to the database. How does this change my connection string. Do I just drop the account and password parameters or do I need to let it know somehow that I want to use Windows Authentication?
Thanks,
Ken Nicholson
View 3 Replies
View Related
Aug 23, 2006
I'm having problems with windows authentication. I'm new to using using Microsoft SQL Server. I just migrated from powerbuilder 8 to powerbuilder 10. I'm using OLE DB and can't get the windows authentication to work within the application. Can anyone help....
View 2 Replies
View Related
Mar 29, 2007
Hi,
I am wondering that do we need to create the separate login at SQL SERVER (server) for all the user using the application for the windows authentication?
How do the windows authentication works? I have a apllication and SQL SERVER (SERVER) running on the same machine. Any user from the same domain can access the application. Application is interacting with the DB through Windows Authentication. Now my question is whether do we need to authenticate each user from the domain at the DB level. That is to craete the login id for all the user for windows authentication.
Thanks,
Rahul Jha
View 1 Replies
View Related
Feb 23, 2007
Hi
How to stop user to login using Windows Authentication.
thanks
asm
View 2 Replies
View Related
Jun 7, 2007
Hi
I remove the windows authentication from enterprise manager.
and Now i want to re-assign the the windows authentication . so can i do it from query analyzer.
asm
View 2 Replies
View Related
Dec 3, 2006
Hi all,You're all stars, really helpful. Maybe one day i can be :)My question is regarding the authentication in Windows SQL Server 2005.We have setup a sa account. We purchase software from a third partythat runs on it. Before their software logs on the sql server as sa,and does its own access control, windows autentication takes over. Itslogs users on correctly i guess, using a more secure link via thedomain. However, I am probably blind, but where can we assign rights toeach domain user? I.e. stop XXY dropping tables, but allow ABC to droptables?I had a brief look through but could not find anything that stood out,in the SQL Manager.ThanksDavidP.S This is SQL Server 2005 Workgroup edition running on a 2003 windowsdomain server. Alongside a SQL 2000 server, which is running fine usingsa accounts.For those in the same timezones as me... have a good evening.
View 1 Replies
View Related
Jul 20, 2005
Hi! Happy New Year!I'm having windows authetication connection problem. Although I cansee all the servers by using SQL Server authentication conncection, Ican see only some of the SQL family servers by using windowsauthentication connection. I checked the servers' properties of that Ican and can't see, but couldn't find any difference. Can someone giveme a direction that will enable me to debugg?If the servers are not in the same domain, how can I see them (windowsauthentication) from the same PC (client)?For some SQL server I even can not login via windows authentication atterminal server. Is any specific setup I should do at server?Thanks!Saiyou
View 1 Replies
View Related
Jul 20, 2005
Hello,We have a SQL 2000 installation (running on NT4 SP6a) with a databaseaccessed by a client application. Currently the application logs into thedatabase using a the SA account. I'd like to move the client access over toWindow Authentication. Each user has their own NT account. What steps do Ineed to take to achieve this?Any help would be greatly appreciated.James G.
View 1 Replies
View Related
Oct 19, 2006
When using Windows Authentication and the Client (xp home edition) has NO PASSWORD and he accesses a server with sqlexpress...does that default him as 'guest'....
What is difference between State 8 Password Mismatch and 9 Invalad password
View 3 Replies
View Related
May 4, 2007
We have an application that uses SQL Server 2005 (Express) as its database server.
We have SQL server and the application database on our server.
To setup the application we need to point it to our database from the clients and specify authentication as either SQL server or Windows Integrated Authentication. We chose Windows Integrated Authentication. This works fine for an administrator user but for a standard user with non administrator privileges , authentication fails and we cant access the database. The client is running Visa Business with UAC on. If I run the application as adminisrator and enter the administartor username and password then I can connect otherwise it is rejected.
How can I can configure SQL server to allow non administrator users to access it using Windows Integarted Authentication.
The server is running SBS 2003
Thanks
View 3 Replies
View Related
Aug 30, 2006
I've got an application that's supposed to use a combination of Windows authentication and Custom authorization, here's how.
Only the administrator can add users, venues, and booking types. In addition, only the administrator can reset a booking password on the bookings table and/or form. I (as the current administrator) want to be able to add a user and grant him/her permissions (either as admin or just user).
Admin access: add new users, venues, booking types, and reset booking password which will be used to grant permission to change booking details such as time and date. When the administrator adds a new user, there's an option (a field called admin on the user table) on whether or not to give the user admin rights, if yes the user is added to both the admin role and the user table
'Normal' user access: add and/or change own booking and change other user's return time and date provided a password is correctly provided. Does not even have to know that there's a user, venue, and a booking type table.
I've read about Windows authentication on the MSDN library but I seem to be getting nowhere and just have no clue where to start.
Help appreciated!
View 7 Replies
View Related
Dec 14, 2007
My organization is in transition of converting from SQL Server 2000 to SQL Server 2005.
Conversion from Visual C++ 6.0 to Visual C++ 2005 is done.
The converted Visual C++ code has OLE DB and has only native code(== no managed code).
The OLE DB uses user name and password information to log into SQL Server 2000.
Microsoft recommand to use Windows Authentication for security reason.
I was informed that Basic Authentication, SQL Server Authentication, or Mixed Authentication
allows OLE DB connectivity with user id and password.
However, I have tried but have not figured out how to use same OLE DB with user Id and password
information to access SQL Server 2005 with Windows Authentication.
Is it possible to use Windows Authentication with OLE DB connectivity with user id and password?
If so, I would greatly appreciate sharing your findings.
Thank you very much for your help in advance.
Paul
View 1 Replies
View Related
