If my SQL Server authentication is windows integrated authentication, whenever my application makes a connection to SQL server does it contact the windows active directory domain controller to authenticate the windows user account? How does windows integrated authentication work in the background?
View 9 RepliesI have observed that a temporary loss of a domain controller can causeproblems creating new ado connections between a client machine runningado and a separate sql server machine that are members of the domain.I understand why this happens when creating connections with windowsauthentication. What is a mystery is that it also sometimes effectsnew connections that use "sql authentication". Below is a descriptionof my test scenario.a. Setup 3 machines.i. one domain controller machine. windows 2000 based. I have triedboth a regular domain setup and a domain setup in compatibility mode.ii. one sql server machine that is a member of the domain. windows2000.iii. one client machine running an ado test program that communicateswith the sql server machine. I have tried both 2000 and xp.b. Start your test ado program and create a connection.c. Fire a query.d. It should work.e. Unplug the network cord on the domain controller.f. Create a new connection and fire a new connection about a minute orso after.g. It should work. Apparently the client caches account informationfrom the domain controller for a certain amount of time. The timeseems to be shorter by default in xp than 2000.h. Wait 20 minutes or more.i. Create another new connection. You will notice a timeout error.If your using windows authentication the timeout will happen 100% ofthe time. That is to be expected. If your using sql authenticationthe timeout seems to happen about 50% of the time. I can't explain itother than some strange Microsoft bug. I speculate that it may besome bug with the way ado caches connections. Perhaps a previouslysetup windows authentication connection gets reused by a request for asql authentication connection. However I haven't been able to proveit.Any insight you can offer would be appreciated. I also have a testprogram you can use to reproduce this behavior if you are interested.Thanks,Frank
View 2 Replies View RelatedI am attempting to set up an always on cluster on VMware for testing. setting up everything through the Failover cluster is fine, the trouble comes when I try to set up the AlwaysOn availability group. Whenever I attempt to specify a network location I receive the following error: Operating System Error 1265(The system cannot contact a domain controller to service the authentication request..).
I looked up this error and most sites point towards a Windows 8 homegroup issue. Since this is a on a domain, I don't think it is relevant. I also added the service accounts from server A to Server B and visa versa. I even added the computer objects to its opposite partner..I have attempted to use "Join Only" and do a manual copy.
I am currently runinng Sql Server 2000 on a Windows 2003 domain controller. I cannot access my local sql server using windows authentication. All i receive is a cannot generate SSPI context error. I understand this is probably related to a security policy issue on the domain controller. Also Sqlserveragent service will not remain running and shutdowns immediately after it is started. This also produces an SSPI context error in the error log. If anyone can help with my configuartion of windows server 2003 such that sql server will recognize a valid sysadmin role for my domain admin account it would be greatly appreciated.
View 1 Replies View Relatedhow to configure sql express 2008 R2 on windows server 2008 R2 domain controller?? and one more thing, what log on should I use for sql express service in sql server configuration manager on domain controller??
View 2 Replies View RelatedHi 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.
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!
I am trying to connect as follows:
Server: Windows 2003, SQL 2005, on a domain
Client: Windows 2008 Beta, not on any domain
I created an account with the same user name as the domain user on the client machine. And then I logged in as that user and went to Manage Network Password. I entered the correct domain credentials. Verified that this worked for file shares. However, SQL does not appear to be recognizing this and it tells me:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I have verified that this domain account is working properly with SQL when the client is also on the domain.
How can I get this Windows authentication scenario to work where the client is not on the domain and the SQL server is on the domain?
I have a very confusing problem that I'm going to attempt to relay concisely:
I have a LAN on which I'm running an Intranet web site built in ASP.Net 2.0 and using SSRS / Sql Server 2005 on the same server.
The name of the box is "warehouse" or "warehouse.mydomain.us." When I ping "warehouse," I get a response from 137.86.166.8, as I should. Likewise, I get a response from 137.86.166.8 when I ping "warehouse.mydomain.us." If I do a "ping -a" on 137.86.166.8, it correctly resolves to "warehouse.mydomain.us." Based on the facts presented in this paragraph, I believe that my DNS is working as expected.
Using IE (6.0.2900.2180.xpsp_sp2_gdr.050301-1519) with "low" security settings, I navigate to http://warehouse.mydomain.us/lex/ (Lex is the name of the app) and it authenticates me to the index page (which calls the SSRS web service to get and display a list of available reports), except that I get the login prompt three times. If I hit F5 (refresh), I get three more login prompts and then I get a 401.1 error. From this point, no matter what I do, I can not see the index page again without closing and restarting IE.
I know that IE only passes credentials to certain sites, so I add my domain (http://warehouse.mydomain.us/) to the Local Intranet list ( Tools->Internet Options->Security->Local Intranet->Advanced) and then I can refresh and see my index page once, but I still get 3 login attempts and then the same thing happens -- I refresh and go through 3 logins and then get a 401.1.
If I now change my address bar to read http://137.86.166.8/lex, I get a single login and it works like a champ. This makes me think that there's a DNS problem, so I open up Mozilla Firefox and wouldn't you know it -- Firefox works like a champ all around -- one single sign-in, it keep credentials between Lex and SSRS, and it appears to use the DNS correctly. However, I can't use Firefox because I can't get any of the SSRS reports to look like they're supposed to in Mozilla-based browsers.
Argh.
I have set my IE securtiy settings to "Low." I have added the primary domain to the Local Intranet list and also to the Trusted Sites list (but you can't do both at the same time). I have even added the internal IP to my "hosts" file.
Does anyone know anything that might be a help to me in getting this figured out?
Other notes of interest:
My machine is an XP machine, but is not on the domain, but I do have a valid domain login that I use and it works in Firefox without problem.
Subject problem has me quite vexed.
I am receiving the following error when attempting to access reporting services... to sum things up real nice and tidy-
I get three login prompts - then the access denied response. It is almost as if it is unable to authenticate the user... anyway... here's the actual error response, I'd really appreciate any input/insight/resolution.
Server Error in '/Reports' Application.
Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.]
System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) +2014163
System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption) +1817
System.IO.Directory.GetDirectories(String path, String searchPattern, SearchOption searchOption) +36
Microsoft.ReportingServices.Diagnostics.Localization.GetInstalledCultures() +112
Microsoft.ReportingServices.Diagnostics.Localization..cctor() +66
[TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.Diagnostics.Localization' threw an exception.]
Microsoft.ReportingServices.Diagnostics.Localization.SetCultureFromPriorityList(String[] localeList) +0
Microsoft.ReportingServices.UI.GlobalApp.Application_BeginRequest(Object sender, EventArgs e) +157
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Hi,
I have a client running in a Domain A and a SQL server running in Domain B using Windows security. We don't want to put SQL Server in the mixed authentication mode.
I created a local group on de SQL server. Gave the group correct rights.
Added users from Domain A to that group. Although he prompted me for username password from Domain A it did work.
There is currently no trust between those domains. What kind of a trust should it be ? I believe that a one way trust where B trusts A should be enough.
Any suggestions.
CE
Hi:
I am trying to figure out if there is a way to connect via SQL Server Management Studio to a server sitting on a separate domain. So here is the situation. there is Server B which sits in a domain called DomainB. If I am in DomainA, I could typically remote into that server utilizing an IP address in DomainB, and even transfer files to that server. But how do I connect via SQL Server Management Studio(basically login as a different domain user) to Server B in DomainB from DomainA. Basically when I choose Windows Authentication from the dropdown list in SQL Server Management Studio, it grays out the username field, which is where I could type something like this domainBusernameondomainB, similar to how you would connect to a share on that server. Please let me know if there is a possibly solution to this. Also, our customer doesnot allow SQL Server Auth/Logins, so that is not an option. Thanks.
We have a SQL 2005 box that requires windows authentication but I have to connect a laptop that is not part of the domain. Is there any way to do this? Per company policy, the SQL server cannot be changed to use SQL Auth AND Windows Auth. Also per policy, the user's laptop cannot be joined to the domain since it's not a company laptop.
I've tried using the following command:
net use \SQLServerNameipc$ /user:DomainNameDomainAccount Password
and then using enterprise manager to register the sql server but it still says that the computer is not trusted.
Is there something else to this that I am missing or is this just not possible?
Thanks in advance for the help.
Hi
We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.
So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.
The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.
For creating the groups I have the following options:
Create a domain group and put all the usergroups from the other domains in this group
Add user groups from all other domains directly into the SQL Server.
Any recommendations here?
Hi,I have a peculiar problem (or maybe not). I have SQL clients installedon XP boxes. These XP boxes are a member of let us say "XYZ" domain.SQL Server is installed on one of the boxes having Windows 2000 Serverin the same network. The installation has been done on local domainaccount. Since SQl Server has been installed with Windowsauthentication, what happens if the XYZ domain fails. Will it bepossible for all clients to access the database present on SQL Serverin the Win 2K box. I think I have made myself clear upto some extent.Please throw light on how to accomplish the connectivity between SQLclients and server in case of domain failure.Regards,Vinodi
View 2 Replies View RelatedHi There
I have service broker working 100% with an initiator - forwarder - target, all in the same domain using windows authentication on the endpoints, for all instances' the sql server service run under the same domain account, which in turn is granted connect on the various endpoints. This all works 100%
However i have a scenario where the initiator will be in a different trusted domain.
I need to know if i am correct in thinking that the initiator sql server servcie account can run under DomainAsqlservice, the target instance sql server servcie can run under DomainBsqlservice, then on the forwarder i simply grant connect on the forwarder endpoint to both DomainAsqlservice and DomainBsqlservice.
Is this correct, will this work?
Thanx
Greetings,
One of my users gets the following error when he tries to connect to my SQL Server 2000 database using windows authentication via Query Analyzer:
[Micorsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Me and the server are located in Colorado and are on the NADomain. User is in London on the EURDomain. The EURDomain has a one way trust to the NADomain to use NADomain resources. I have granted access to the database to the user via Enterpise Manager as EURDomainuserid. All the literature I've read says this should be sufficient to connect but isn't. User can connect with SQL Server authentication. Users on the NADomain in Toronto can connect just fine with Windows Authentication. EURDomain user can access other file server resources in the same building as the SQL Server in Colorado.
SQL Server version is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
EURDomain Client ODBC version is 2000.85.1022.00 and MDAC is 2.8.
Any help is greatly appreciated.
Novice wants to learn why it is not recommended to install sql 2005 express on to a 2003 domain controller. I have installed sql 2005 express on a 2003 domain controller and when I tried to run management studio it failed to run. there seems to be no problem with the engine, oh I also installed books online I wonder... can there be an issue with the books online and management studio I remember there were problems in the beta era. Now I am wondering if it's a good idea at all I feel like I have been left at train station with all my luggage, I have all this equipment... work stations, a server, printers and no resolution to my problem. most of all I want to learn why I shouldn't install sql on a domain controller... can someone please explain in detail.
dbarselow
Arnie I have sql express, 2003 R2 as a domain controller, a laptop I use as a remote connection to the server. What I want to accomplish is to connect to a website using the server as a branch headquarters type of connections. I was told to look into using sharepoint services. I noticed my server has this service. Can't I use sql in this type of senario. I want to be able to have content uploaded to the web site by verious employees then have that content scanned and cleaned if needed and made available for my server. And from there I would have what I need to continue my publishing the content.
View 1 Replies View RelatedI have a problem with OLAP running on a Server that is domain controller, the problems is that after the Server was promoted to Domain controller, all users are allowed to see all databases and cubes, I did the same test on another server and found same problem after upgrading to domain controller.
I already tried demoting the server as domain controller to member server, but did not work.
Anyone knows how to revert this thanks,
Ronnet :mad:
One last question ( for today)...
The recomindation to not install SQL express on a Domain Controller. Is there a specific technical reason or is it just the general don't use the DC has a Terminal server / sql server / application server line of thought?
The reason I ask is we have been using SQL 2000 on a W2k3 server configured as a DC as well as for Terminal Services. (yes I am aware of the security risks) but in a small network with 5 or less ft clients and 30 thin, I can't justify a seperate DC and App server.
Kay
I have 2 questions, the first one is more of a DC/AD issue, but I don't think there is a forum for that. But that question leads directly into my other question which involves SQL Server. I hope someone can give me some feedback.
We have a small network of about 12 users, and we are running SBS 2003 as our DC and Exchange server. We are going to start doing a lot of heavy lifting with data so we got another box to run Server 2003 Standard and SQL Server 2005 Standard. But now it's been suggested that we also install a second DC on the new SQL box. I am opposed because a DC should be on it's own box, and the SQL Server will need all of the available resources. I don't really think we need a second domain controller anyway, considering we have such a small office and good backups.
So my questions are: does anyone have an opinion as to whether or not we really need a second domain controller in our 12 person Small Business Server environment. I'm thinking there wouldn't be a whole lot of benefit. We certainly don't need it to share the load, and a SBS dc cannot give up it's FSMO roles so i don't see how a second dc would be able to properly serve as a failover should the first one go down.
My second question is: what kind of impact on the SQL Server would there be by installing a DC? I know it's not Microsoft recommended to have them on the same box.
Thanks,
-Rick
I want to install SQL Express 2K5 on a lightly used second Windows 2003 SP1 DC. I know about the warning not to do this type of install. However, the group is small and their budget does not allow for another server just for one application. With that being said, the error message I keep getting is "can't create local groups......" (I know that you do not have local groups with AD). So, is there a work around to this issue or can someone point me in the right direction as how to install on a domain controller? Thanks in advance for your time!!!
View 3 Replies View RelatedHello everyone, I have searched the forum regarding this topic and have found numerous good threads about installing SQL Express on a DC. My question is, I am assuming that if installing SQL Express on a Domain Controller is not recommended, that installing the MSDE 2000 on a Domain Controller is not recommended either?
Thanks in advance to all replies!
Tim
Hi
I have a customer that was using Sql 2005 Express on a W2k3 server. They then decided to turn the server in to a second domain controller. When they did this Sql Express stopped working, I'm guess because the account the service was running as nolonger existed (Network Service account). Because of this they demoted it back to just a member server, but still the sql express service wouldn't start.
When I look at their server now I have noticed that the Sql2005$ accounts Sql express created during installation are all missing.
What I want to do now is set the Sql express setvice to use a domain account (using the SqlServer configuration manager), but I'm concerned that because the sql2005$ groups are missing things won't run as expected.
Should I be worried about these accounts?
What would happen when it becomes a domain controller anyway, won't these local account disappear and hence my new sqlexpress service account wouldn't have permission to the sql server data folders??
Thanks for your help
Graham
Hello Everyone,
I saw a lot of webcast about security in Sql Server and many of those talk about that is not recomendable install Sql Server on Windows Server Domain Controller. Actually, I don't know Why not? Someone can explain me, please?
Thank youª!!!
Julian Castiblanco P.
ITPros-DC
Bogotá,Colombia
Any one know the answer?
View 1 Replies View RelatedI have SQL 2005 installed in a virtual (ESX) environment with a separate DC. Every minute or so an event shows up in the Application Event Log that says:
Type: Failure Audit
User: dgtestdc1$
Computer: sql1
Source: MSSQLSERVER
Category: (4)
Event ID: 18456
Description:
Login failed for user 'dgtestdc1$'. [Client: <ip address>]
Data includes: SQL1 master
Any idea what is causing this and how to fix it?
Thanks,
Mindy
SQL Server 2005 SP2, installed as a default instance at the domain controller.
SSIS connection failed with the message
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
The RPC server is unavailable.
No firewalls, client and server in the same subnet, ping is ok.
I suspect the issue is similar to described here: http://support.microsoft.com/kb/940232
I've given the user all the rights to DCOM MsDTSServer, but I cannot include him to the local "Distributed COM users" group because the domain controller doesn't have local group at all.
I have included the user into the "domain admins" group, after that user got a connection. But this is not good, you know what I mean. Does any other solution exists, without reinstallation SQL Server to another server or giving the administrator's rights to the user?
Thank you.
I've just got RS Enterprise Edition.
Installed it in a Test Server, ok.
Installed it in a DEV server, having some errors.
My DEV server is an "ALL in 1" server, which is also a Domian Controller.
While Test Server is not a DC.
The installation was succesful but when I open RS Manager thru the web, it gives me a ASP.NET error:
Access to the path "D:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportManagerin" is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.UnauthorizedAccessException: Access to the path "D:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportManagerin" is denied.
ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.
I tried doing everything I can possible think of from the error message advice, including adding rights to ASPNET user from ISS to that particular folder...no success.
What I'm suspecting is I can't install RS in a DC box. But I can't find this info anyway in the web.
Anyone can point me to the correct direction or any advice?
I would like to install SQL server 2005 cluster on Windows 2003 cluster. Is domain controller (AD and DNS) necessary for the SQL server 2005 cluster? If yes, may I install it on one of the SQL server 2005 cluster nodes? Thanks in advance
View 2 Replies View RelatedHi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
View 4 Replies View RelatedWe've encountered a problem on one of our SQL servers running integrated security where MS Security Manager errors out with "An error occured executing sp_addlogin using Domain_nameusername - " is not a valid name since itbegins with an invalid character." We think it is because the domain has the underscore character in it name. Can anyone confirm or point to other possible configuration issues?
View 1 Replies View Related