We are about to change the sa password, currently all packages and jobs rely on this account. I imagine there is probably a better architecture that we could employ to ease this process. Any suggestions recommendations?
Also any caveats I should be aware of regarding places to look that might currently rely on the sa account so that we do not need to worry about existing processes from breaking?
I think we are going to create an NT account for DTS Packages and possibly use the same account for any DTSRun jobs, does this make sense? Or is there anything to gain by having these as separate accounts? Also should this be the same account used to run the MSSQLServer process?
I tried doing a search for this information here, thinking it was already covered, but could not find anything that informative, any resources that you could point me to would be appreciated, I will look on BOL as well as MSFT to see what I can dig up.
We're getting an error where we can't add a login with the full dns name of a user - domain.xyzuser, for example. Get an error 15401, "Windows NT user or group domain.xyzuser' not found". The domain has a different Netbios name and DNS domain names, so we can add the user when we use the form "netbiosnameuser". So far so good.
Unfortunately, we have another application - Office Share Point Server whose shared services provider won't run, giving errors in the event log every 60 seconds that "Windows NT user or group 'domain.xyzuser' not found".
It looks as if SQL insists upon listing users in the form netbiosdomainnameuser, and applications that look for domain.xyzuser simply fail to authenticate.
I'm trying to harden our SQL Server environment to improve overall security of our systems. I would like to check what specific permissions are granted to SQL Server, SQL Server Agent and SQL Server Integration Services account. I've tried with the "sc qprivs <service_name> " command, but it returns an empty privilege list. Is there a way to retrieve the permissions using a cmd or powershell command?
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 !!!
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
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."
I am currently hardening our SQL 2012 (with AlwaysOn Availability Groups) environment. Both the SQL service and agent account are using service accounts (only domain user). SQL browser service is disabled. Permissions to all roles are handled by using domain groups.
Currently a lot of (default) NT Service accounts are listed (some with sysadmin privileges). Are there accounts that can be removed?
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
Hi,Apologies for not knowing much around this subject, we currently run sqlserver 2000 for a database and have each user marked up on the database.We are moving to use a windows security group, so taking themanagement slightly away from the database server.What I would like to know is, will this effect functions such assuser_sname() by returning the group name instead of user id? I wouldassume not but it's worth checking!Also, I'm guessing if a user is marked on the database as their own idand as part of a security group, then all permissions are thrown in withdeny taking preference? How does this work with a database owner who isalso part of a security group that has limiting functionality?Thanks for your help,Chris
We're using windows server 2012 R2 and sql server 2014 standard with mixed mode authentications. When we connect to other windows server using windows authentication and hit the following error:"the target principal name is incorrect cannot generate sspi context"..is the above err related to mixed mode authentications at installation?
Does SQL Server have any place to configure an account Lockout Threshold (SQL-authentication), also is there a default value (how many times failed logging would be initiated before the user lockout?)
On my current project we have a requirement to re-authenticate the user when accessing sensitive information.
We want re-authenticate using standard NT logins against Active Directory (not a custom database or SSO.
This is trivial to configure using Basic Authentication, however I would like to use an ASP.NET login form. I would like to avoid writing a security extension as I do not want to perform custom authentication. I would like the web service to use it's built in authentication and authorisation mechanisms.
Is the above possible???
The following gives some more details about wht I've tried.
I have tried configuring the report server/manager with Forms authentication as in the sample but cannot get it to work with out implementing the security extensions.
I changed the web.config files and the policy files for permissioning my dll with FullTrust. I did not configure an extension as I want reporting services to use it's built in windows security mechanisms.
In the login page code behind I call the ReportingService2005.LogonUser() method which always throws the following exception: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: --
Reporting Services Error
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) Get Online Help
SQL Server Reporting Services --.
I check the log file and it has the following:
at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.logon_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) aspnet_wp!library!18!03/13/2007-11:38:23:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.FormatException: Input string was not in a correct format. at System.Text.StringBuilder.FormatError() at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.Samples.ReportingServices.CustomSecurity.Logon.ServerBtnLogon_Click(Object sender, EventArgs e) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationLogon.aspx.cs:line 130 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace --- at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.logon_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) --- End of inner exception stack trace --- aspnet_wp!library!18!03/13/2007-11:38:24:: i INFO: Exception dumped to: c:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson aspnet_wp!library!1!03/13/2007-11:39:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Could not load Authentication extension; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
Am I missing something? Is this even possible? If not, then why isn't it possible?
It feels like I've been going round in circles on something that shouold be pretty trivial to configure.
Wa have a user that comes over to our BI department, on a test-pc he logs in with his domain account, goes to the /Reports url and sees the report we prepared for his usercode. The virtual directories have Windows Authentication in IIS.
When he goes to his desk, where he is running with the same account he can't see these reports ....
What could be the problem and where should i start looking ??
When logged onto a server and connecting using windows authentication (either by server name or localhost) I am getting in fine, but when I connect using the IP I get the following error.
I can log in using SQL Server authentication with the IP.
In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domainx and schema exactly domainx).
I created a DDL Trigger on my DB to find out who and how it is created , but I didn't get any useful information.
I change the owner of schema that I can delete the schema and after that delete the user, and sometimes because the user is owner of a service , I have to alter the owner of the service to DBO and delete the user , but again and again exactly this user and schema (e.g domaninx ) are created as soon as I delete it.
I have read these topics : [URL] .... and [URL] .... but can't solve my problem.
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. Â
Greetings. I have developed our own custom security extension, implemented forms based authentication, and can authenticate from report manager, report server and sql studio. So far so good.
However, when it comes to Authorization, i'm unclear in a few areas and would appreciate if someone could help me out with the following questions. It should be noted that in the code I have granted an administrator user full access to all operations and permissions, and then tested against both an administrator user and a normal user.
"Returns the set of permissions granted a specific user for an item in the report server database."
Inparticular, the secDesc parameter is supposed to contain the security descriptor associated with the item.
However, with our extension this parameter is always null, even if I have already granted access for a user, which is confirmed through logging in CreateSecurityDescriptor.
Through the report manager or sql studio I can see that the permissions have been created, so I can't understand why I never see them in the GetPermissions method? This then (seems to) flow through to the various CheckAccess methods, where the users are authenticated, but are not authorized to perform any operations. i.e. in report manager a user has no folders or reports available.
Is RS authorization designed around the concept that the details will always be stored in it's own database?
Ideally, we'd like to have the various roles, users and function authorizations defined in our own security framework. This is working great for the authentication aspect of the extension, but unless there is a mechanism which exposes the details of the particular authorization process (e.g. the name of the folder being viewed or report being run), then I can't see a way we can implement it. Unless i'm missing something fundamental of course!
Using Report Builder as the Administrator user (or any other user), I can see no data models available, even though I have created them via Report Manager, and I get the following exception trying to open up the list of reports:
System.Web.Services.Protocols.SoapException: The permissions granted to user '' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction.Execute() at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
I have implemented a report server proxy (inherited from ReportService2005) as per the example, to pass through the authorization cookie. Any clues as to what could be wrong?
Finally, I suspect part of my problem may be in assignment of users to System Roles ("System Administrator" and "System User"), I'm not sure if these are meant only for Windows Authentication? I can see no way of assigning these roles to any of my users using Forms Authentication.
does anybody know why MS doesnt consider Basic Authentication a viable solution for Inter/Xtra net access to RS? I'm re reading the documentation at http://msdn2.microsoft.com/en-us/library/bb283249.aspx but so far am not getting what the Security Extension options accomplish that can't be accomplished with much less effort using some form of Basic Authentication.
Hi folks, ya all are just fine i hope. I've a question regarding indexes. I've a table that is very often updated. My boss asked me to keep a log of each record when the table is updated. I've created an AFTER UPDATE trigger on the table and throw the old record into a log table on each update. The table would get large earlier. I want the updates on the original table fast, so i don't wanna add any kind of indexes on the log table. The log table will be scarcely queried only if the user has made a mistake and we want the old record back. Now, i've column named event(DATETIME) log table that records CURRENT_TIMESTAMP. Should i create a clustered index on this column so the records get in sequence (the values in the column will be unique due to time and seconds being stored). I could be able to access records fastly in the log table on the basis of EVENT column. But how much it would cost the updates on the original table?? My major concern is to keep the updates on original table fast while logging the old records. Usually it's said not to created indexes on date fields. Do i need to create non-clustered index or i am following the correct path.
We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.
I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.
What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.
I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------- The SPROC being exposed as the webmethod: -
The computer in question is our dev server and is running as a workgroup machine with the following: - Win Server 2003 SQL Server 2005 .net Framework 2.0 No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
What is exactly the recommended way of using a SqlCeConnection object in your application?
In all the examples I see (IBuySpyDelivery for example) it opens the connection object in the constructor and leaves it open most of the time until the class gets disposed.
Is that the way to do it? Or should you open and close the connection for each database action (insert/select/delete) you're doing on the local SQL Mobile database?
I'm a bit confused, but maybe I'm messing up the way of working on remote database servers with how it should be done on a local SQL Mobile db.
Hello all, We have some doubts regarding NSTL guidelines for motorola Q. Our application is managed application. Which ocasionally connects to server. It is very helpful if we can get answers urgently.
Hopper Test €“ How can we make it run on Smartphone 5.0 (Motorola Q)? Is this tool only applicable for pocket pc 2003? Application Verifier Test Tool €“ Is this tool only applicable for unmanaged code? If we are using .NET as the development environment, do we still have to use this tool? Call Interupts €“ Is their a special way of handling Call Interrupts in the application or the default behaviour provided by the device (Moto Q) is OK? Send Key (Green key) on the device €“ What is the expected behaviour of this key when the application is running? Do we have to handle it separately in the application Is their a maximum space limit allowed for creation of the database files for an application? Is their any specific utility like MaxfileCNT which NSTL uses for checking the behaviour of the application when there is no space available on the device? thanks & regards, Rashmi
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"
as an error. This is what I've got:
I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider. SQL Server 2005 Surface Area Configuration Database Engine Remote Connections €“ set to €œLocal and Remote Connections €“ Using TCP/IP Only€? SQL Server Browser is Enabled and Running - is set to Active, under Built In Account €“ Network Service I have created Windows firewall exceptions for: sqlservr.exe sqlbrowser.exe udp port 1434 SQL Server Configuration Manager both SQL Server and SQL Server Browser are running. Under SQL Server 2005 Network Configuration Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration Shared Memory 1 TCP/IP 2 Named Pipes 3 all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
I folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
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
(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.
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?
