SQL Security :: SPN Account On More Than One Instance
Oct 28, 2015Can you use a SQL service account (domain account) on two different SQL instances?
Can you set the SPN for both clusters instances with the same account?
ADVERTISEMENT
What Is Security Account Delegation (was Hi)
Feb 26, 2005can any 1 give info on "Security Account Delegation"
thanks in advance
No Mapping Between Account Name And Security ID
Jul 4, 2007Hi,I created a user account on my active directory service. I then triedto assign a service located on my SQL server to be executed by thisaccount. However, when I try to configure my SQL server service, Iget the following error message:WMI Provider Error"No mapping between account name and security ID was done"Do you know what I am doing wrong?thanks
View 1 Replies View RelatedService Account And CLR Security
Aug 6, 2007
By default does CLR code run under the SQL Service Server account or the SQL Agent Service Account? Does anybody have a link to BOL or MSDN???
My assumption is its under SQL Server Service Account.
I'm trying to satisfy the DBA's security concerns in regards to CLR Code. If the account it runs under (Agent or service) has zero privliges will a dba still be able to maintain the server? Wouldnt all their backups work under a privilaged account that isnt the SQL Server Service Account?
Double posted in security.
Security Account Delegation
Oct 22, 2006Hi world,
I have a question, but first I need to give you some background:
My network works with Active Directory on Windows 2000, and I have web servers running on windows 2003 and SQL Servers 2000 running on Windows 2003.
I wanted to enable account delegation and I found a bunch of information.
Everything seemed "easy", but I tried to test it first on my test servers anyways and this is what happened:
We created the SPN for the SQL Server
Account is trusted for delegation check box was selected for the service account of SQL Server.
Account is sensitive and cannot be delegated check box was not selected for the user requesting delegation.
But when we checked the box Computer is trusted for delegation (and only this box !!) in the server running an instance of SQL Server 2000, the role of this server changed magically (just like this guys, it was magic) from "server" to "Domain Controller".
We were intrigued about this change, but we "trusted" the white paper that we had in front of us.
http://support.microsoft.com/kb/319723
After some hours, the production web servers (of the whole network) and many workstations stopped working:
The IIS on this web servers will show an empty list of websites
The network and dial-up connections were missing on the web servers and also on the workstations.
The web servers and the workstations affected were "isolated" from the network, the command ping was not finding any of this computers.
Anyway, it was a nightmare, it took a while to fix the mess, we reverted the changes in Active Directory, and this makes me thing that the magical "promotion" of the SQL server to Domain Controller had to do with all this.
the questions is:
Do you have an idea about what could have caused all this? I mean, I still need to enable this account delegation thing. But I would like to know first if someone has done it before in a similar environment or if someone has run into one of the problems described before.
Thanks world.
Proxy Account Security.
Apr 26, 2007
Hi experts,
Is there any potential security threat using Proxy accounts in SQL Server 2005 ? If any , Please give URLs for reference.
Thanks,
DBLearner
Security Login Account Restore
Oct 25, 2007
Hey Everyone,
I am testing restoring databases on another SQL 2005 server in out environment using HP data protector 5.5 and its great. However, I notice that the security login accounts do not get restored. If this is the case how do I go about getting accounts restored? Also, are there any other options?
Cheers,
Mark
No Mapping Between Account Names And Security IDs Was Done.
Jun 7, 2006I received the following when trying to deploy an 2005 analysis services package over an existing database:
The following system error occurred: No mapping between account names and security IDs was done.
We have redeployed this solution several times over the last week and have never encountered this error. The changes that we are deploying are related to partitioning of the measure group fact tables - and are not related to security in any way. Can someone assist?
Security Problem Running Xp_cmdshell From Non-sa Account
Dec 17, 1999Our system is MS SQL Server v7 and NT 4. We have a stored procedure that exec's xp_cmdshell to run an external program located on the server. When a user who has 'sa' rights runs this stored procedure it works fine. When a 'non-sa' user (via the "BuiltinUsers" NT account) runs it, xp_cmdshell produces the following error:
Msg 50001, Level 1, State 50001
xpsql.c: Error 1385 from LogonUser on line 476
Is there an NT security or SQL Server setting I've overlooked that can be changed to allow non-sa users to xp_cmdshell programs?
n.b. The BuiltinUsers account does already have execute permission on the xp_cmdshell procedure.
Urgent - No Mapping Between Account Names And Security IDs Was Done Error !
Feb 28, 2007Hi There
When i go to configuration manager and change the sql server service to run as a domain account i get the following error:
No mapping between account names and security IDs was done.
This is Sql Server Express running on a domain controller - Windows Server 2003 R2.
Everything i find ont he net refer to IIS, DHCP etc etc , i cannot find the issue regrading sqls server configuration manager.
Thanx
SS2000 Error: No Mapping Between Account Names And Security IDs Was Done
Oct 25, 2007Hello all;
I am trying to form a replication system but at the very beginning i couldn't pass an obstacle.
While trying to create the Replication it says i have to change the user which starts the SQL Agent because the current starter user account is a system account and this will make the replication between servers fail.
"SQL Server Agent on OZN currently uses the system account, which causes the replication between the servers fail. In the following dialog box, specify another account for the service startup account."
I change it in the properties dialog box of the SQL Server Agent. The new account is the one I formed and granted accordingly. But it gives the following error when I try to apply the changes.
" Error 22042: xp_SetSQLSecurity() returned error -2147023564, 'No mapping between account names and security IDs was done' "
I tried many things, searched in the net, changed the owner of the database, applied new accounts, many grants, applied service pack 4, etc...
If anyone helps it will be very much appreciated. Thanks in advance...
Setup And Upgrade :: No Mapping Between Account Names And Security ADs Was Done
Aug 26, 2015Our software vendor rep is trying to upgrade MS SQL server 2008 SP4 to 2012 SP1. Get an error message: no mapping between account names and security ADs was done. He says that we get this error message because we have two domain controllers in our network, and one is running on the same windows server that run sql server. Out IT support disagrees to delete the second domain controller, saying it is recommended by Microsoft and he suggests that the problem is in Active directory.
View 2 Replies View RelatedRsconfig Command Format For Local Named Instance - Setting Up An Unattended Account
Dec 27, 2007my local instance of reporting services is named and therefore I think causing me a problem when I issue the following command to set up an unattended account...
rsconfig -s localhostinstance name -e -u domain nameuser name -p password
the message I keep getting is "No Reporting Services instance found on local host.". I tried a couple of things including replacement of the word localhost with my computer name but to no avail. I tried single and double quotes around the -s parameter but no success.
Anybody know how to do this?
Failure Setting Security Rights On User Account SQLServer2005BrowserUser${computerName}
May 29, 2007I'm trying to install SQL Server 2005 Express on a Windows 2000 server, but I'm getting the following error message:
"Failure setting security rights on user account SQLServer2005BrowserUser${computerName}"
Can anyone help me please?
SQL Security :: Cannot Connect To Local Instance
Sep 25, 2015I have verified that the following services ARE running.SQL Server (SQLEXPRESS)SQL Server Browser SQL Server VSS Writer.This one however will not start ... For some reason it starts then automatically turns off.SQL Server Agent (SQLEXPRESS).When I try to connect using my <machine name>/instance and Windows Authentication I get the following error ...
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)".The connection specified in the "Connect to Server" box was working perfectly fine before I upgraded. I thought it might have been my Norton 360 Premier but I uninstalled it.I AM having issues with the adapter frequently dropping the internet connection but I just disconnect and reconnect and that resolves itself.The other technique I tried was to replace the server name with the IP address ... <192.168.0.22>/Instance ... This actually seemed to find the SQL Server but rejected the Windows Authentication ...
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication." I have several databases on here and would hate to have to reinstall SQL Server and manually hook them back up.
Whether To Use Local System Account Or Domain Account For Service Account
Jan 5, 2006During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedSQL Security :: How To Change IP Address Of Clustered Instance
May 7, 2015In our lab we have 2 clustered instance of Sql server 2018R2 as follows
Virtual Server Name
IP SqlVir 10.1.1.6
SqlVirDr 10.1.1.12
The Data is SqlVir is replicated manually every day to SqlVirDr.
We had to change the Virtual Server name of SqlVirDr to SqlVir so that all dot net applications accessing SqlVir could continue to access the database without changing the application string.
For that purpose I deleted the computer name SqlVir from the domain and its IP 10.1.1.6 from DNS. Then I went to the failover cluster manager of SqlVIrDr right clicked the Sql services selected the properties and changed the DNS name from SqlVirDr to SqlVir.The applications then could access the data.However when I changed the network IP address from 10.1.1.12 to 10.1.1.6 the Sql services was found to be down.
Perhaps the procedure I followed in deleting the computer name from domain and the IP from DNS was wrong.What exactly is the steps that I should follow to achieve the above objective.
SQL Security :: Rename Clustered Instance And Change Its IP Address?
Apr 22, 2015We have an Sql Server 2008R2 Clustered production instance by name 'ProdVir' configured in 2 nodes(Active-passive) withWIndows Server 2008 R2. We also have another clustered instance as disaster recovery by name 'VirDr' configured again in another 2 nodes of Windows Server 2008 R2. Every day morning there is mainatenace plan which backups all the database in production and another maintenace plan in the disaster recovery server 'VirDr' which restores the backups into the VirDr instance.
I would like to know that in an eventuality of a disaster in the clustered production instance of 'ProdVir'. could we rename the the instance VirDr(meant for disaster recovery) to ProdVir and also change the Ip addresses accordingly so that the application programs do not have to change the details for the datasource in the connection strings.
SqlBulkCopy Instance Via SQLEXPRESS-ADO.NET 2.0-Visual C# Express: Security Was Unhandled?
Aug 28, 2007Hi all,
I copied the following set of C# code statement from a website to learn the SqlBulkCopy instance via SQLEXPRESS-ADO.NET 2.0-Visual C# Express:
/////////////////////----Main.cs---//////////////////////
sing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace SqlBulkCopySample
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}
private void btnStart_Click(object sender, EventArgs e)
{
String sourceConnectionString =
"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
String destinationConnectionString =
"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";
DataTable data = SelectDataFromSource(sourceConnectionString);
CopyDataToDestination(destinationConnectionString, data);
}
private DataTable SelectDataFromSource(String connectionString)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SelectOrders", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data.Load(reader);
}
return data;
}
private void CopyDataToDestination(String connectionString, DataTable table)
{
SqlBulkCopyColumnMapping mapping1 =
new SqlBulkCopyColumnMapping("OrderID", "ID");
SqlBulkCopyColumnMapping mapping2 =
new SqlBulkCopyColumnMapping("ShipName", "Name");
SqlBulkCopyColumnMapping mapping3 =
new SqlBulkCopyColumnMapping("ShipAddress", "Address");
SqlBulkCopyColumnMapping mapping4 =
new SqlBulkCopyColumnMapping("ShipCity", "City");
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 5;
bulkCopy.ColumnMappings.Add(mapping1);
bulkCopy.ColumnMappings.Add(mapping2);
bulkCopy.ColumnMappings.Add(mapping3);
bulkCopy.ColumnMappings.Add(mapping4);
bulkCopy.DestinationTableName = "DataMySqlBC1";
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
bulkCopy.NotifyAfter = 200;
bulkCopy.WriteToServer(table);
}
void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show
(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
}
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above project in my local PC (with Adiministrator previlidge/use) that is in our LAN/Network system and I got an error: SecurityException was unhandled - Request for permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'failed. First, I have a question: is this problem related to the remote connection to SQLEXPRESS? Second, I do not know how to solve this problem. Please help and answer my first question and tell me how to solve this problem.
Thanks in advance,
Scott Chang
Cross DB Dialog Security Issues. Was: Can't Route To Another LOCAL Broker Instance
May 31, 2007Hi Remus,
I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.
The situation is as follows:
DB1 owned by login1, has a user for login2; this DB is for the initiator
DB2 owned by login2, has a user for login1; this DB hosts the target
Both DB's have TRUSTWORTHY flag set to ON
Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.
Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?
==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how???
BTW, SELECT @@VERSION shows that I'm on build 3054, in case it matters.
Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!
Best regards,
Hugo Kornelis
SQL Security :: What Windows Account Used Server Login To Access Server
May 14, 2015If we have a "pool" SQL login, a one that uses SQL Server authentication, and this login is used by different domain account to access SQL Server, is there a way to audit which domain account used that "pool" login to do something on a object in SQL Server? I have to keep this way of accessing SQL Server, so how to create a login for every domain account accesses SQL Server
View 7 Replies View RelatedSQL Security :: Log Ship Database In Server Instance In One Domain To Database
Jul 2, 2015I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
View 6 Replies View RelatedSA Account (DBA System Account) Granting Priveleges But SQL Server 2000 Not Applying Them
Dec 4, 2006I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
DTS Fails As A Job With Service Startup Account As "System Account"
May 9, 2002I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Xp_cmdshell Does Not Execute For Non-sysadmin Account Even With Proxy Account
Mar 2, 2004Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Domain Account Vs Local Account For SQLServerAgent
Jul 20, 2005Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedDomain Account Without A SQL Login Account
Apr 25, 2007I have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
SQL Server Admin 2014 :: Cannot Connect To Named Instance (2nd Instance) From Local SSMS
Jul 22, 2015I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.
How Can I Migrate All Objects Alogwith All Constraints,SP,Triggers Etc From Development Instance To Production Instance
Jun 13, 2006Hi fellows,
I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.
can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.
This is Sql server 2000 Cluster environment.
thanks in advance for any help
rahman
SQL 2012 :: Clustered SSAS Unable To Connect To Named Instance (instance Not Found On Server)
Mar 20, 2014I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
Migrating From SQL2K Named Instance To SQL2K5 Default Instance
Feb 19, 2007Hi, I have a task in hand to migrate (upgrade) from SQL2K named instance to SQL2K5 default instance. There are many intranet applications touching current SQL2K. I would like to perform this upgrade such that I don't have to touch any application code - meaning I don't have to change the connectionstring to point to new Default instance. How can I achieve this?
So, in otherwords, here is what I want to achieve:
Current Server: SQL2K: SERVER_AINSTANCE_A (named instance)
Upgraded Server: SQL2K5: SERVERB (default instance)
If I have both default, I could achive this by setting up DNS alias after migration done so that any call for SERVER_A would point to SERVER_B. But in my case, I don't have SERVER_A, I have named instance. Is there any solution?
Regards,
Vipul
How To Move A Single Database From SQL Server Instance To New SQL Instance
Jan 13, 2007Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
How To Move A Single Database From SQL Server Instance To New SQL Instance
Jan 13, 2007Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu